Print ERD and Normalization solution and hand in to the teacher in the class. One document per group is required. I will go through it in the class. If any group member is not present will receive 1% penalty. In each problem Normalization is 15 marks each and ERD is 10 marks each.
Problem 1: travel expense database design
See the expense report form below. Design the database to support it and bring the tables to
3NF by answering the following questions.
- Based on the expense report, start with the following original table schema:
Expense (StatementNumber, EmployeeID, Name, Title, Email, Department, Manager, StartDateOfTrip, Nbdays, TripPurpose, ExpenseLineNumber, ExpenseDate, Account, Description, Vendor, Category, PaymentMethod, Amount). Consider (StatementNumber, ExpenseLineNumber) as PK. Draw the dependency diagram. Make sure you label the transitive and/or partial dependencies.
Account refers to a general ledger (GL) account created to hold expense information. Every single type of expense has a GL code or account that is composed of department and type of expense. For instance, an employee working in the IT department (has id 10) has to enter the airplane expense (category T with ID 100) in the first line of the expense report, the GL account is then 10100.
Definition: A general ledger is a complete record of financial transactions over the life of a company. The ledger holds account information that is needed to prepare financial statements, and includes accounts for assets, liabilities, owners equity, revenues and expenses.
- Write the relational schemas and create a set of dependency diagrams that meet 3NF requirements. Rename attributes to meet the naming conventions, and create new entities and add attributes as necessary.
- Draw the Crows Foot ERD. You can use VISIO, Word, etc.
Problem 2: Bus stations database
Have a look at the Bus/Train route/line schedule example for
Bolton/Malton/North York route provided on gotransit.com. Use the following schedule table schema as a startup to answer the following questions.
| routeNum | stopId | StopName | StopType | SeqOnRoute//SEQUENCE ON ROUTE | Depaturetime | stopLocation | status |
| 31 | 101 | Union Station Bus Terminal | B | 1 | 09 00 | 141 Bay Street, Toronto, ON | On time |
| 31 | 102 | York Mills Bus Terminal | B | 2 | 09 00 | 4023 Yonge St., North York, ON | On time |
| 31 | 103 | Yorkdale Bus Terminal | B | 3 | 09 00 | 1 Yorkdale Road, North York, ON | On time |
| 31 | 104 | Bloor GO | T | 4 | 09 00 | 1456 Bloor Street West, Toronto, ON | On time |
| 31 | 105 | Weston GO | T | 5 | 09 00 | 1865 Weston Road, Etobicoke, ON | On time |
| 31 | 106 | Etobicoke North GO | TB | 6 | 09 00 | 1949 Kipling Ave., Etobicoke, ON | On time |
| 31 | 107 | Malton GO | TB | 7 | 09 00 | 3060 Derry Rd. E., Mississauga, ON | On time |
| 31 | 101 | Union Station Bus Terminal | B | 1 | 09 30 | 141 Bay Street, Toronto, ON | On time |
| 31 | 102 | York Mills Bus Terminal | B | 2 | 09 30 | 4023 Yonge St., North York, ON | On time |
| 31 | 103 | Yorkdale Bus Terminal | B | 3 | 09 30 | 1 Yorkdale Road, North York, ON | On time |
| 31 | 104 | Bloor GO | T | 4 | 09 30 | 1456 Bloor Street West, Toronto, ON | On time |
| 31 | 105 | Weston GO | T | 5 | 09 30 | 1865 Weston Road, Etobicoke, ON | On time |
| 31 | 106 | Etobicoke North GO | TB | 6 | 09 30 | 1949 Kipling Ave., Etobicoke, ON | On time |
| 31 | 107 | Malton G O | TB | 7 | 09 30 | 3060 Derry Rd. E., Mississauga, ON | On time |
| 38A | 102 | York Mills Bus Terminal | B | 1 | 17 20 | 4023 Yonge St., North York, ON | Delayed |
| 38A | 103 | Yorkdale Bus Terminal | B | 2 | 17 20 | 1 Yorkdale Road, North York, ON | Delayed |
| 38A | 104 | Bloor GO | T | 3 | 17 20 | 1456 Bloor Street West, Toronto, ON | Delayed |
| 38A | 105 | Weston GO | T | 4 | 17 20 | 1865 Weston Road, Etobicoke, ON | Delayed |
| 38A | 106 | Etobicoke North GO | TB | 5 | 17 20 | 1949 Kipling Ave., Etobicoke, ON | Delayed |
NOTE: TB corresponds to TRAIN AND BUS STATION
If time does not have a value in a row, it means the bus or the train does not stop at that stop.
- Given the above table structure, define the PK and justify your answer. Draw the dependency diagram. Label all transitive and/or partial dependencies. (Hint: This structure uses a composite primary key.)
- Remove all partial and transitive dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created.
- Draw the Crows Foot ERD.
- We need to list all the stops per city. Alter the Stop table structure to allow such listing.
Reviews
There are no reviews yet.