MySQL Forums - Sample Database (Sakila)

Where Dell's sample database (no replies)

bib go — Sat, 21 Nov 2009 10:26:12 +0000

Where Dell's sample database was designed to represent an online DVD store, the Sakila sample database is designed to represent a DVD rental store. The Sakila sample database still borrows film and actor names from the Dell sample database.

Development was accomplished using MySQL Query Browser for schema design, with the tables being populated by a combination of MySQL Query Browser and custom scripts, in addition to contributor efforts (see Section 6, “Acknowledgements”).

The Sakila sample database (no replies)

bib go — Sat, 21 Nov 2009 10:24:56 +0000

The Sakila sample database was designed as a replacement to the world [http://downloads.mysql.com/docs/#examples] sample database, also provided by MySQL AB.

The world sample database provides a set of tables containing information on the countries and cities of the world and is useful for basic queries, but lacks structures for testing MySQL-specific functionality and new features found in MySQL 5.

The Sakila sample database (no replies)

bib go — Sat, 21 Nov 2009 10:24:09 +0000

The Sakila sample database was developed by Mike Hillyer, a former member of the MySQL AB documentation team, and is intended to provide a standard schema that can be used for examples in books, tutorials, articles, samples, etc. The Sakila sample database also serves to highlight the latest features of MySQL including Views, Stored Procedures, Triggers, etc.

Database Design for a forum (no replies)

Cameron Moore — Thu, 12 Nov 2009 08:40:18 +0000

So here's a rough sketch of what I was thinking would work for the database of a forum. I have a few questions though. First, is putting all the posts in one table too slow? Would it be better to have different post tables for different forums. Also I want to include searching in this forum(obviously) so I was thinking of using fulltext on the text column under posts. Only problem is then I will have to use MyISAM, which means I can not do transactions. Anyway to get around this, because when someone posts a message I want to also tell the author of the post that there has been a reply.

Anyway here is a rough sketch:

Forums:
id(int)(primary), name(varchar)(unique), createdeleteboard (int-refers to some security table issuing moderator, officer, or member status etc.)

Boards:
id(int)(primary), forumid(int)(index), name(varchar)(unique), desc(varchar)(unique), created(bigint-seconds since epoch), viewsecurity(int), deletethreadsecurity(int), createthreadsecurity(int), stickythreadsecurity(int)

Threads:
id (int)(primary), boardid(int)(index), title(varchar)(unique), authorid(int)(index), created(bigint), stickied(int)(0 or 1 value), views(int), viewsecurity(int), replysecurity(int), deletepostsecurity(int)

Posts:
id(int)(primary), threadid(int)(index), text(mediumtext)(fulltext), authorid(int)(index), imageid(int), created(bigint)

Also I was thinking each User should have their own thread tables, which has threads that they started. this way I can alert/keep track of replies to their thread.

UserPosts:
id(int)(primary), threadid(int)(index), lastpostid(int)(index)

SQL Command Error for other ver (no replies)

basuki muhamad — Thu, 05 Nov 2009 04:09:24 +0000

Dear Friend,...

I have SQL command as follows:

select r.no_reg kode,r.nama regional,jt.nama jenis,format(sum(t.jumlah),2) realisasi
from regional r,jenis_type jt
left join pasar_real t on r.no_reg=left(t.no_org,2 ) and jt.no_typei=left(t.no_type,3) and
t.no_akt ='03001' and t.tgl between '2009-01-01' and '2009-02-30' where
r.no_reg<>'00' and r.no_reg<>'08' group by r.no_reg,jt.no_typei

so.... if I use Mysql Ver. 4.1 there's not any problems but if I use Mysql Ver.5 problems begun by order :

Error Code : 1054
Unknown column 'r.no_reg' in 'on clause'

please help !!!

more example databases (no replies)

Marco Marcellis — Thu, 29 Oct 2009 13:02:46 +0000

On www.java-task.com you can find in each task an example database.

Importing dbspaces from ASA database (no replies)

Software guy — Mon, 19 Oct 2009 09:34:13 +0000

Hello All,
I am very new to MySQL.
In my java based application i was using ASA(Adaptive Server Anywhere) and now we are upgrading to MySQL database. And as of now all my codes are based on my dbspaces which is in the ASA database.Is there anyway to import my dbspaces from ASA to MySQL?

Please help me!!!

Thank You!!!!

hi (no replies)

margr atbritho — Wed, 30 Sep 2009 03:10:41 +0000

This is website very good

---------------------------------------
please contact me
----------------------------
erewrjry@sycodas.cz
edf@sycodas.lt
erewr@sycodas.lv
erwrf@sycodas.md
erwer@sycodas.me
dfrfg@sycodas.pl
xczdxc@sycodas.sk

both primary and unigue key at rental table (no replies)

Nikos NA — Tue, 29 Sep 2009 23:21:23 +0000

Why is there a UNIQUE KEY consinsting of 3 columns rental_date, costumer_id, inventory_id in the rental table? I believe that there should be a reason because indexes use drisk drive.

(I saw in the changelog that this UNIQUE KEY was the old primary key and it has been replaced, I believe for performance reasons, but why keep it?.)

Create Table to insert time values from 00:00:00 to 23:00.00 (no replies)

Lamso Traore — Mon, 28 Sep 2009 19:29:13 +0000

Hello all,
i'm really knew in database field and I need some help. I have in MySQL database a table "employee" with columns: work_date, week, dayname, shift_id. I would like to define an additional table and insert the time values:

00:00 01:00 02:00 03:00 4:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00

How to create a time table, insert these values and join the table with employee table?

Thanks

sample db on our website (no replies)

Tomas Ramirez — Wed, 23 Sep 2009 21:43:28 +0000

we put the sample mysql db up on our website http://www.windwardreports.com for our users to test their reports and reporting apps. we know little stuff like setting up your sample db can really make life hell, and we're trying to get rid of all of that tedious work.

That's why we created the Windward Report Engine. Companies need a powerful, secure enterprise-level Java/J2EE reporting engine to back their critical reporting and document generation systems.

The Windward Engine installs easily, runs on one or more servers as part of your server-based application, and can easily produce hundreds of thousands of reports per day on a single server. The server is a pure Java product (and also is available as pure .NET).

Even better news for you guys: When combined with the Windward report design tools AutoTag and AutoTag Max, users design, schedule and run reports from within Microsoft Office. That means you can turn over reporting to the business group and get back to more interesting work.

You don't have to take my word for it. Check out this robust Java reporting engine for yourself.

How do i disable foreign key contraints using mysql 5.1? (no replies)

Chris Tolentino — Tue, 22 Sep 2009 01:28:03 +0000

I've tried

set foreign_key_checks =0

but it has not worked. any ideas?

foreign keys of actor table not listed (no replies)

Terrence Brannon — Tue, 25 Aug 2009 07:47:38 +0000

the CREATE TABLE statement for actor does not reference film_actor.actor_id

how to create dinamics droplist with mysql (no replies)

Benjamín Vera — Wed, 19 Aug 2009 23:12:08 +0000

Hello world.... jejeje Like a first program.....

I have a manu that I need to show/hide the options below in the menu. I have a tree in the link-menu but I have a problem when I want to show the 3rd. options y the menu.

This is my complete code:
----------------------------------------------------------------------------------
In the file "prueba06.php" I have that:

Prueba06

menu_desplegable_base_de_datos

include("conexion.php");
$COD=$_REQUEST['cod_cat'];
$CATEGORIA=$_REQUEST['categoria'];
$registros=mysql_query("select * from categoria ORDER BY cod_cat");
while($reg=mysql_fetch_array($registros)){
$COD=$reg['cod_cat'];
$CATEGORIA=$reg['categoria'];
?>

include("conexion.php");
$COD=$_REQUEST['cod_sub_cat'];
//$CATEGORIA=$_REQUEST['categoria'];
$registros2=mysql_query("select subcategoria from subcategoria where categoria='$CATEGORIA' order by cod_sub_cat ");
while($reg2=mysql_fetch_array($registros2)){
$COD=$reg['cod_sub_cat'];
$CATEGORIA=$reg['categoria'];
$SUBCATEGORIA=$reg2['subcategoria'];
?>

}
?>

include("conexion.php");
$COD=$_REQUEST['cod_sub_cat_1'];
$registros3=mysql_query("select subcategoria_1 from subcategoria_1 where subcategoria='$SUBCATEGORIA' order by cod_sub_cat_1");
while($reg3=mysql_fetch_array($registros3)){
$COD=$reg['cod_sub_cat_1'];
$SUBCATEGORIA=$reg2['subcategoria'];
$SUBCATEGORIA_1=$reg3['subcategoria_1'];
?>

}
?>

}
?>

----------------------------------------------------------------------------------

In the File "conexion.php" I have that....
$dbhost="localhost"; // host del MySQL (generalmente localhost)
$dbusuario="root"; // aqui debes ingresar el nombre de usuario
// para acceder a la base
$dbpassword=""; // password de acceso para el usuario de la
// linea anterior
$db="test"; // Seleccionamos la base con la cual trabajar
$conexion = mysql_connect($dbhost, $dbusuario, $dbpassword);
mysql_select_db($db, $conexion);
?>

----------------------------------------------------------------------------------
I´m working in Windows Vista, Xampp and Joomla. I'm in the experimental phase proyect.

In my case tu access to MySQL I need to type in the Prompt (cmd)
c:\cd c:
C:\Archivos de programa\xampp\mysql\bin>mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 188
Server version: 5.1.33-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

show databases;
+---------------------------+
| Database |
+---------------------------+
| information_schema |
| test |
+---------------------------+

use test;
Database changed

Aquí ya procedo a crear mis tablas:

create table categoria (
cod_cat int(2) NOT NULL auto_increment,
categoria varchar(40) default NULL,
primary key (cod)
);

create table subcategoria (
cod_sub_cat int(2) NOT NULL auto_increment,
categoria varchar(40) default NULL,
subcategoria varchar(40) default NULL,
primary key (cod)
);

create table subcategoria_1 (
ccod_sub_cat_1 int(2) NOT NULL auto_increment,
subcategoria varchar(40) default NULL,
subcategoria_1 varchar(40) default NULL,
primary key (cod)
);

Comprobar

show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| categoria |
| subcategoria |
| subcategoria_1 |
+------------------------+

Insertar datos en mis tablas

insert into categoria (categoria,) values ('categoria-1'),('categoria-2'),('categoria-3'),('categoria-4');

insert into subcategoria (categoria,subcategoria) values ('categoria-1','subcategoria-1');
insert into subcategoria (categoria,subcategoria) values ('categoria-2','subcategoria-2.1'),('categoria-2','subcategoria-2.2'),('categoria-2','subcategoria-2.3');
insert into subcategoria (categoria,subcategoria) values ('categoria-3','subcategoria-3.1'),('categoria-3','subcategoria-3.2'),('categoria-3','subcategoria-3.3'),('categoria-3','subcategoria-3.4'),('categoria-3','subcategoria-3.5');
insert into subcategoria (categoria,subcategoria) values ('categoria-4','subcategoria-4.1'),('categoria-4','subcategoria-4.2'),('categoria-4','subcategoria-4.3');

insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-1','CP jera'),('subcategoria-1','ritos'),('subcategoria-1','caque'),('subcategoria-1','colín'),('subcategoria-1','dencia'),('subcategoria-1','ula'),('subcategoria-1','relos');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-2.1','cion');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-2.2','Rep.Norte'),('subcategoria-2.2','Rep.Golfo'),('subcategoria-2.2','Rep.Paci'),('subcategoria-2.2','Rep.Cen');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-2.3','Ref-yta'),('subcategoria-2.3','Ref-ero'),('subcategoria-2.3','Ref-tlan'),('subcategoria-2.3','Ref-nca'),('subcategoria-2.3','Ref-inaCruz'),('subcategoria-2.3','Ref-ula');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-3.1','Hidro.SUR'),('subcategoria-3.1','Hidro.SUROESTE'),('subcategoria-3.1','Hidro.NOSOESTE');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-3.2','arell'),('subcategoria-3.2','MAZA');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-3.3','PolChuc');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-3.4','urgos');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-3.5','Luna');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-4.1','Sosa'),('subcategoria-4.1','Srrey'),('subcategoria-4.1','Sreón'),('subcategoria-4.1','Sahua'),('subcategoria-4.1','Scala'),('subcategoria-4.1','Smanca'),('subcategoria-4.1','Sjara'),('subcategoria-4.1','Srpio'),('subcategoria-4.1','Uterrey'),('subcategoria-4.1','Urpio'),('subcategoria-4.1','Sdoza'),('subcategoria-4.1','Sruz'),('subcategoria-4.1','Stlán'),('subcategoria-4.1','Senas'),('subcategoria-4.1','Sdero','2009-08,('subcategoria-4.1','Sex'),('subcategoria-4.1','Snando'),('subcategoria-4.1','Smico');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-4.2','960'),('subcategoria-4.2','961'),('subcategoria-4.2','964'),('subcategoria-4.2','965'),('subcategoria-4.2','970'),('subcategoria-4.2','971'),('subcategoria-4.2','975'),('subcategoria-4.2','977'),('subcategoria-4.2','980'),('subcategoria-4.2','981'),('subcategoria-4.2','982');
insert into subcategoria_1 (subcategoria,subcategoria_1) values ('subcategoria-4.3','Cex'),('subcategoria-4.3','Cmex'),('subcategoria-4.3','Ctus'),('subcategoria-4.3','Centa'),('subcategoria-4.3','Conche'),('subcategoria-4.3','Cca'),('subcategoria-4.3','Clcos'),('subcategoria-4.3','Curgos'),('subcategoria-4.3','Cque');

I have a manu that I need to show/hide the options below in the menu. I have a tree in the link-menu but I have a problem when I want to show the 3rd. options y the menu.

To send me an advise or solutions please send me a mail in: benjamin
LO QUE NO ME ESTÁ JALANDO ES EL SEGUNDO LINK (ARBOL) YA QUE NO ME FUNCIONAN PARA TODOS.... Intuyo que mi problema está en la tabla subcategoria_1 ya que solo me le el primer grupo y todos los demás grupos me muestran/ocultan el PRIMER GRUPO....
Cómo puedo solucionar mi problemita....?????

Saludos y espero también les pueda servir de algo este código.....

Please do not use spaces in column names (1 reply)

Terrence Brannon — Tue, 18 Aug 2009 19:24:39 +0000

the view 'customer_list' has a column with a space in it: 'zip code' which is created in sakila-schema.sql as follows:

CREATE VIEW customer_list
AS
SELECT cu.customer_id AS ID, CONCAT(cu.first_name, _utf8' ', cu.last_name) AS name, a.address AS address, a.postal_code AS `zip code`,
a.phone AS phone, city.city AS city, country.country AS country, IF(cu.active, _utf8'active',_utf8'') AS notes, cu.store_id AS SID
FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id
JOIN country ON city.country_id = country.country_id;

I would appreciate it if this column did not have a space in it.

Thank you for all your hard work in putting together this sample data. I greatly appreciate it.

Retreving last distinct highest record in mysql (2 replies)

hrishi patil — Wed, 21 Oct 2009 02:19:32 +0000

hello,

tblquoteproposal

id requestid QuotePraposalLink comment
1 8 xyz1 rifsf
2 8 xyz2 ri2sf
3 9 xyz3 ri3sf
4 9 xyz4 ri4sf

In this table requestid is foregin key.
there is also other table tblrequest which has requestid as primary key.

I WANT TO RETREIVE last(LATEST)_distinct highest REQUESTID Ie. ID 2 & ID 4;
I HAVE WRITTEN FOLLOWING QUERY BUT IT DOES NOT GIVE ME LATEST RECORD.

select r.RequestId,r.ConsultantId,(select concat(FirstName,' ',LastName) from tbluser where UserId = v_userId) as "Customer",
r.RequestDate,r.QuoteDetailsFileLink,r.Requestcomment,r.CustomerId,qp.QuotePraposalLink,qp.Comment
from tblrequest r
left outer join tblquoteproposal qp on r.RequestId=qp.RequestId where r.customerid=v_userId group by r.RequestId order by qp.id ;

Include Database automatically / silent (1 reply)

Sun, 09 Aug 2009 15:03:10 +0000

hello everybody,

is there a possibility to inlcude a database automatically/silent?

for example with a bat file or something else?

thank you for all answers

y3Game (no replies)

gracy smith — Sat, 25 Jul 2009 08:45:19 +0000

Game description: Best and Latest Version of the Classic King of Fighters Series game including Ryu.
- Released all 7 characters,with 5 hidden type characters
- Added new effects
- Added new stages
- Improved "Simple Key Mode", launching skills with one key is possible
This game is an extremely large file (14.59 Megabytes), and will take a long while to load.
Please be patient.
Y3 is a directory of free mini games in flash and shockwaveWe are adding new fun games every day so please bookmark this page and come back! Unlike other sites, we don 't throw tons of ads and popups at you. Our servers are not overloaded so the games we have load fast!
naruto games
dallas fha loans

Merging Rows in a single table (1 reply)

Eddie Felip — Sun, 09 Aug 2009 15:34:47 +0000

Hi there experts,

I'm doing a project called Time and Attendance. My table for the Time in/out looks like this.

id, badgeid, dateandtime, comments.

the output will be like this.

id | badgeID | dateandtime | comments
----------------------------------------
1 | 1001 | 03/07/09 09:30 AM | Check-In
2 | 1002 | 03/07/09 10:00 AM | Check-In
3 | 1001 | 03/07/09 06:30 PM | Check-Out
4 | 1002 | 03/07/09 07:30 PM | Check-Out

I want to have a report like this.

id | badgeID | Check In Time | Check Out Time
-----------------------------------------------------
1 | 1001 | 03/07/09 09:30 AM | 03/07/09 06:30 PM
2 | 1002 | 03/07/09 10:00 AM | 03/07/09 07:30 PM

I need to merge two rows with the same date to make two columns for INs and OUTs.

Please advice.

Edz

Mysql & Php source code and tutorial (no replies)

sweta rathi — Sun, 12 Jul 2009 09:15:27 +0000

Mysql & php source code and tutorial form beginner to expert level all free
by best professional developer.

Read from here

http://www.sourcecode4you.com/

Need help in compelx query (1 reply)

Martynas Eskis — Sat, 06 Jun 2009 08:06:31 +0000

Hello, I need to make quite complexed query:

I need to select 'name' and 'link' and 'orde'r from 4 different tables and order them by 'order' column. dificult is that there is no similar values to make (where table1.id = table2.id). I need just put thees values in one SQL result but it must be ONE query. What sould I use ?

Sakila Sample DB only partially installs (no replies)

Steve Grigsby — Tue, 26 May 2009 17:52:34 +0000

Hello All

I am new to this and trying to learn MySQL. I downloaded the sakila sample DB to use with the book I have. I am using MySQL 5.1

Here is what I have run into. Using SQLyog Enterprise Trial the Sakila Sample Database Schema doesn't install about half of the tables and other things as well. Here is an example of one Create Table command and the resulting error message. When I compare the script to the ones that did run I cannot find a syntax error.

CREATE TABLE customer (
customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
store_id TINYINT UNSIGNED NOT NULL,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
email VARCHAR(50) DEFAULT NULL,
address_id SMALLINT UNSIGNED NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
create_date DATETIME NOT NULL,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (customer_id),
KEY idx_fk_store_id (store_id),
KEY idx_fk_address_id (address_id),
KEY idx_last_name (last_name),
CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=INNODB DEFAULT CHARSET=utf8;

Here is the accompanying error message:

Error Code : 1005
Can't create table 'sakila.customer' (errno: 150)

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

Also MySQL Query Browser gives me this error msg as well:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CREATE TABLE customer (
customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT' at line 1

Any help would be appreciated since I just blew two hours on research and can't find the answer.

Thank you much!!!

DATABASE MANAGEMENT SYSTEM (1 reply)

pertos petrou — Tue, 05 May 2009 12:56:33 +0000

I need imidiate help for this project!

I am a computer science student here in Cyprus but I don’t have a clue what its happening over here.

Is it possible for anyone to help me????

Please contact with me at my e-mail emaantou1986@hotmail.com

If you are willing to help me

Thank you all in advance

· The project should be implemented using SQL commands that could be issued from Microsoft Access, or ORACLE Product SQL*PLUS or some other SQL environment.

Deliverables:

The list of delivables to be handed in (both hard copy and electronic copy) on or by the hand-in date is as follows:

The Case Description. In 2-5 pages produce a description of the case under study along with the main user requirements. Expand the case given by suggesting queries and other system requirements which you deem useful to end-users.

The database model (Entity Relationship Model plus supporting narrative, including any assumptions made). For the design of the ERM you should use an appropriate CASE tool and any one of the notations covered in the course.

A Relational Schema, which comprises listings of table definitions clearly identifying primary keys (underlined) and foreign keys (in italics).

The code and results of data definition and manipulation (i.e. table creation, data insertion and the required SQL queries) including the appropriate test data.

CASE: WoodWorks Ltd.

WoodWorks Ltd. is a wood products manufacturer in Cyprus. WoodWorks specializes in manufacturing all kinds of high-quality furniture components, adapted to the specific needs of individual customers (tailor-made).

WoodWorks’ product range includes “Root-like WoodWorks” (furniture components manufactured from wood layers, surfaced on chipboard, MDF or plywood). It also includes molded plywood, and other woodworking, based on customers’ own designs and specifications. WoodWorks’ products are delivered in a semi-finished form and are used where high-quality requirements is a must. Furniture manufacturers and carpenters use them in the production of their own specialized woodworks. Architects, interior designers, and specialized furniture manufacturers also use “Root-like WoodWorks” to make superior quality furniture and decorations. WoodWorks’ products have several specialized applications for the production of exclusive products, including decorative frames, furniture parts, hotel, restaurant and bank decorations, shop windows, dinner table tops, office worktops, kitchen cabinets etc.

WoodWorks buys most of its raw materials from its mother company. In this way it manages to keep a relatively small stock of raw materials, thus having a cost advantage. All prices of raw materials are determined in accordance with a special agreement (cost plus system) between the two companies. WoodWorks also buys a small part of its raw material requirements from sources outside the group.

WoodWorks’ production process is initiated when a customer places an order by supplying the company with his/her product design, choice of wood to be used, dimensions and other specifications. The products ordered by individual customers normally differ in size, type of surface (wood layer or melamine), basic material (chipboard, MDF or plywood), finish, etc. Because of these specific characteristics of the orders, each order’s price is calculated individually. Obviously, the need arises for a quick and reliable costing system in order to facilitate the management of the company to quote the correct price to the customer, to estimate the profitability of the operations, and to help the pricing policies of the company. The current system is a manual one handled by the company’s production manager who makes tentative cost estimates of the main production elements, i.e., raw materials, labour, plant usage and other operation costs. Subsequently, depending on this costing, the manager gives a price to the customer who decides whether to place the order or not.

The top management of the company suspects that there might be a considerable degree of inaccuracy in this system, and this may cause serious problems to the business profitability, endangering the company’s long-term viability and its market position vis-à-vis existing competition from other local or foreign suppliers. They have therefore requested that we implement an automated production costing system.

For each order the following information will be recorded:

Raw materials’ content - Types, quantities and costs of the materials required for the production of the specific order.
Labour content - Number of workers, number of man-hours and hourly rates involved.
Plant contents - Types of machines, machine hours and hourly machine costs required.
Overhead contents - An additional percentage on top of the previous categories reflecting the overhead costs of the company, which cannot be directly allocated to any of the three categories above.

The system should initially keep the cost estimation and quotation given to the customer and then the placement of an order, if the customer decides to proceed with one. It should also offer the possibility of monitoring actual costs after the completion of an order, for reasons of verification, comparability and gradual improvement of its accuracy. The system will provide valuable information to the top management for reasons of cost monitoring, price quotations and marketing policies.

Data should be maintained about employees, customers and suppliers. The system should also keep track of products, and available inventory, customer requests, quotations, and orders from customers and of course purchases from suppliers. For the completion of the product quotation there should be input made concerning employee man-hours, machinery usage and raw materials needed in order to calculate the total cost of the specific order. The system will then add a percentage for overhead costs to get the total cost. The involvement of specific employees (of different specializations) in the execution of each order will be recorded so as to know the task(s) assigned, at first, and then completed by each employee. After an order has been executed, the user should add the actual cost of the specific order to the system, in order to compare actual costs with estimated costs.

(Case created using data about Xyloform Ltd., a subsidiary of Cyprus Forest Industries Public Ltd. http://www.cfi.com.cy/index.php )

Requirements for the case study (deliverables in detail)

1. Develop an entity model to support the above scenario. You may be required to make some assumptions. Please state and explain these in your documentation.

Your model should comprise:

I. An E-R diagram clearly showing the maximum and minimum cardinality for each pair of related entities. (30 marks)

II. A normalised relational schema derived from the E-R diagram including appropriate attributes associated with each entity, clearly showing the primary key of each relation and the foreign key where applicable. A data dictionary describing the type, size and meaning of each data element or field is also required. (20 marks)

Note:

· You may make suitable and intelligent assumptions.

· Anything that is not stated as part of the requirements may be assumed.

· Your assumptions must NOT override any user requirement.

· All assumptions must be clearly stated or explained in your report.

2. Set up tables, including well-designed test data, to implement the application, and identify appropriate integrity constraints to help ensure that data is entered with correct values. You must make sure that your sample data covers all requirements of the queries in section 3 below. Therefore queries that yield null results/ output will not gain full marks even if the SQL code is correct. The code for creating and inserting data into the tables/relations must be provided. (20 marks)

3. Suggest meaningful end-user queries to extract data from your database. Set up and test all queries using an SQL product. Describe each query and provide both the SQL code and the output of the query in hard copy and on disk. (3 marks each- Total marks: 30 marks)

You are required to describe and then satisfy a minimum of 10 queries extracting data from your database. These queries should vary from simple (single-table, one or more search criteria) to more advanced (multiple-table, using join, subqueries, sorting, functions, views, etc

how to connect jsp with wamp server (no replies)

ravi reddy — Fri, 10 Apr 2009 09:50:27 +0000

Hi everyone, i am new to this forum.
I am using WAMP server to process PHP scripts,there i am able to connect php with MYSQL database.
Recently i am developing a project on Java JSP, is it possible to use same database(mysql) for jsp's also?
if so how to connect?
please help me

Status Sakila sample database (no replies)

Marcel Overdijk — Sun, 15 Mar 2009 11:46:30 +0000

I was wondering if the Sakila sample database is still maintained?

A example front-end would be nice.

Sakila does not install (2 replies)

Russell Taylor — Fri, 10 Jul 2009 12:24:41 +0000

I cannot get the Sakila database uploaded to my MYSQL Server v5.1 OEM version. I keep getting syntax errors using a remote session to the server. I have used every possible syntax variation of the instructions downloaded from this site and have spoken with an experienced DB Administrator who says what I am doing should work. I am using a Win XP Service Pack 2 version PC to access the server using the command interface client as the instructions demand. Help!

No result back (no replies)

Jaap de Aap — Wed, 25 Feb 2009 14:22:45 +0000

I have a mysql database with the following colletion utf8.
In the database are row with en without umlauts, for example field there ar three field with the name place with has the value "Kölsch'

If i made the following query
$query = "SELECT * FROM WHERE (Place = 'Kölsch')";
In mij sql part of mysql editor then i got 3 rows back and this is good.

but when i do the same query
$query = "SELECT * FROM WHERE (Place = 'Kölsch')";
$result = mysql_query($query)
or die("Fout bij uitvoeren query");

While ($row = mysql_fetch_assoc($result)){
echo "$row[Place]
";
}

in a .php document then it goes wrong, then it displays no rows.

Can somebody help me?

Need a solution on .sql files (2 replies)

Bhaskar ** — Tue, 31 Mar 2009 20:02:48 +0000

In my project i have to execute .sql files, nearly 50 are there at present iam executing them by using cmd like this. mysql> source file1.sql;
..
..
mysql> source file50.sql;

My requirement is to execute multiple .sql files at a single shot, can anyone know solution for this,help me.

Thanks & regards,
Bhaskar

LOADING EMPOYEES DB INTO MYSQL (1 reply)

Eldad Antwi-Bekoe — Tue, 31 Mar 2009 20:27:30 +0000

Hi Guys,

I'm so new to this I'm ashamed to ask. I'm trying to load employees DB from launchpad. I've followed every command in the manual and varied my syntax but I keep getting the same error message. I used this syntax at the mysql prompt as stated in the manual:

mysql> $ mysql -t < employees.sql;

Please can someone let me know what I'm getting wrong here. I feel so stupid.

Many thanks

HELP! confusion with n*m tables - mysql database creation (no replies)

Pavan Kataria — Mon, 02 Feb 2009 18:49:21 +0000

Hi, I've been using PHP and MySQL for a month or so basically im a beginner.
im going to get straight to the point.

This is the concept:
A website that randomly selects a male and a female... and a user can rate out of say 0-10 how likely it is they are going to argue.. just an example so you understand the concept!

For example... a picture of John Patel pops up and a picture of Shantelle Rocks.... the current user then gives them a rating of 7. Many users can log on and rate random selections..
Now John Patel.. can log on and see that many users have given a rating of him and Shantelle Rocks... and can see the statistics of how likely it is they would argue.. soobviously it would be represented in a percent value.
Shantelle Rocks can also do the same and log on her account and see that people have given a percentage of her and John Rocks.
They both each can also see other people they have been compared to and the percentage raiting will also be shown next to them.

I know that i need two tables. One for the list of males and on for the list of females.
one person from each table is randomly chosen ready for a user to rate...

This is the problem:
Where would i store the percentage values?
I could create a new table for each selection.. But if n is the sum of all males... and m is the sum of all females... then the number of tables needed to create JUST to store the percentage values between each male and female selection pairs...would be n*m tables.
which is not a good way to go about doing it.. i think...!

I hope there is an easier way to store these percentage values...
Can anyone help me out on how to create such a system?
Im sure theres an easier way to store these percentage values of each pair that pops up.
Thanks in advance