This is an exercise. You don't have to show or hand in anything, but if you have any problems or questions, you are welcome to ask your teaching assistant.
There are no solutions to this exercise.
Unfortunately, the previous DBA has left for South America, and won't be back. She took all the documentation with her, or maybe she never wrote any. Your first job as DBA is to re-create the documentation from the schema and the content of the database.
On a bad telephone line from Tierra del Fuego, just before the batteries in her mobile phone died, the database administrator has told you this:
|
The company that the database describes is a chain of department stores
(in Swedish: en varuhuskedja).
It consists of a number of stores (Swedish: varuhus),
and each store contains a number of departments (Swedish: avdelningar).
The company has a number of employees (Swedish: anställda), who sell items (Swedish: varor) at the stores. Each sale is registered in the table sale (Swedish: försäljning) in the database. The items are bought from a number of suppliers (Swedish: leverantörer). The company also uses a number of computer parts (Swedish: datordelar). Each shipment (Swedish: leverans) of parts is registered in the table supply. |
A human-readable presentation of the tables and the data in the database is available here.
A file with SQL commands that will create the demo database in Mimer is available as create-demo-database.txt. Use your Mimer database, called dbksomething, to hold the tables. The file can be run using BSQL's read command, for example like this:
As additional help, you find some old receipts in a box. They seem to correspond to what's in the sale table:read all input from 'C:\Documents and Settings\tpy\Skrivbord\create-demo-database.txt';
Receipt number: 100581
Date: 1975-01-15
Store: Oakland (number 7)
Checkout: Jones, Tim (number 157)
Payment method: cash
Towels, Bath (number 118) Quantity: 5
Twin Sheet (number 120) Quantity: 1
Total items sold: 6 items.
Welcome back!
|
Receipt number: 100582
Date: 1975-01-15
Store: El Cerrito (number 8)
Checkout: Smith, Paul (number 1110)
Payment method: Credit card number 24356540
Earrings (number 26) Quantity: 1
Total items sold: 1 item.
Welcome back!
|
Receipt number: 100586
Date: 1975-01-16
Store: El Cerrito (number 8)
Checkout: Evans, Michael (number 35)
Payment method: Credit card number 54096831
Clock Book (number 106) Quantity: 2
Total items sold: 2 items.
Welcome back!
|
Receipt number: 100592
Date: 1975-01-17
Store: Oakland (number 7)
Checkout: Thomas, Tom (number 129)
Payment method: cash
Shirt (number 258) Quantity: 1
Total items sold: 1 item.
Welcome back!
|
Receipt number: 100593
Date: 1975-01-18
Store: San Francisco (number 5)
Checkout: Edwards, Peter (number 13)
Payment method: Credit card number 11652133
1 lb Box (number 23) Quantity: 2
Total items sold: 2 items.
Welcome back!
|
Receipt number: 100594
Date: 1975-01-18
Store: San Francisco (number 5)
Checkout: Collins, Joanne (number 215)
Payment method: Credit card number 12591815
Jacket (number 52) Quantity: 1
Total items sold: 1 item.
Welcome back!
|
As you can see from the create table commands, the previous DBA didn't declare any integrity constraints. There are no primary key or foreign key declarations. Decide which declarations are needed, and apply them to the database.
When you add the primary key and foreign key declarations, you may discover errors in the data in the database. For example, perhaps someone who was manager for a department has left the company, and been deleted from the employee table, but then nobody remembered to update the dept table?
In SQL, you can change the definition of a table with the alter table command, to add or remove columns, and to add or remove primary key or foreign key constraints. However, Mimer SQL seems to have a bug that causes certain commands of the type alter table add foreign key to fail. Therefore, we suggest that you modify the file that contains the create table and insert into commands, and re-create the entire database by re-running that file. Just remember to put some drop table commands first in the file, to remove the old versions of the tables.
Summary: