Topic: Database, IT

Part A

The Security office at College X keeps a list of parking tickets which have been issued to vehicles parked illegally on the campuses.

This is the portion of the table for one semester:

Part B

NOTE: You can also draw the tables and ER diagrams by hand!

STID LName FName LicNo Ticket# Date Code Fine Phone No St Lic 111-7804 FL 38249 Brown Thomas BRY 123 TRE 141 82453 Green Sally 391-1689 AL 15634 16017 14987 16293 17892 10/17/10 2 11/13/10 1 10/05/10 3 11/18/10 | 1 12/13/10 2 $25 $15 $100 $15 $25 a) Convert this table to a relation in first normal form (1 NF) by entering appropriate data in the table. What are the determinants in this relation? [5 marks] b) Draw a dependency diagram that shows all the functional dependencies in the relation, based on the sample data. [2 marks] c) Give an example of one or more anomalies that can result in using this relation. [3 marks] d) Develop a set of relations in third normal form (3NF). Include a new column with the heading Violation in the appropriate table to explain the reason for each ticket. Values in this column are: expired parking meter (ticket code 1), no parking permit (ticket code 2), and handicap violation (ticket code 3). [5 marks] e) Develop ER diagram with the appropriate cardinality notations. [5 marks] a) Create database CollegeDB and from the final set of relations, create tables using DDL statements. [1+6 = 7 marks] b) Insert 5 records in each table. [3 marks] c) Write SQL queries for the following questions: - [5X2=10 marks] i. How many students got ticket with code "2"? ii. List the STID and Name of all students who got a ticket between 01/01/10 and 10/01/10 (Format is mm/dd/yy) iii. List all the students in alphabetical order by student name. iv. How much is the total fine collected between 01/01/10 and 10/01/10? (Format is mm/dd/yy) Count the number of students to whom the parking ticket was issued by Licence Type (St Lic). V. STID LName FName LicNo Ticket# Date Code Fine Phone No St Lic 111-7804 FL 38249 Brown Thomas BRY 123 TRE 141 82453 Green Sally 391-1689 AL 15634 16017 14987 16293 17892 10/17/10 2 11/13/10 1 10/05/10 3 11/18/10 | 1 12/13/10 2 $25 $15 $100 $15 $25 a) Convert this table to a relation in first normal form (1 NF) by entering appropriate data in the table. What are the determinants in this relation? [5 marks] b) Draw a dependency diagram that shows all the functional dependencies in the relation, based on the sample data. [2 marks] c) Give an example of one or more anomalies that can result in using this relation. [3 marks] d) Develop a set of relations in third normal form (3NF). Include a new column with the heading Violation in the appropriate table to explain the reason for each ticket. Values in this column are: expired parking meter (ticket code 1), no parking permit (ticket code 2), and handicap violation (ticket code 3). [5 marks] e) Develop ER diagram with the appropriate cardinality notations. [5 marks] a) Create database CollegeDB and from the final set of relations, create tables using DDL statements. [1+6 = 7 marks] b) Insert 5 records in each table. [3 marks] c) Write SQL queries for the following questions: - [5X2=10 marks] i. How many students got ticket with code "2"? ii. List the STID and Name of all students who got a ticket between 01/01/10 and 10/01/10 (Format is mm/dd/yy) iii. List all the students in alphabetical order by student name. iv. How much is the total fine collected between 01/01/10 and 10/01/10? (Format is mm/dd/yy) Count the number of students to whom the parking ticket was issued by Licence Type (St Lic). V