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.

1. 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.

1. 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.
2. Draw the Crow’s 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.

1. 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.)
2. Remove all partial and transitive dependencies, draw the new dependency diagrams, and identify the normal forms for each table structure you created.
3. Draw the Crow’s Foot ERD.
4. We need to list all the stops per city. Alter the Stop table structure to allow such listing.