ITM 500 Data and Information Management - Ryerson University - ITM500 Final Exam

The Acme Pizza Company has engaged you to develop a database to facilitate the delivery of pizzas. To this end draw a logical data model for the following situation. Use the methodology from the Erw in software. Show all cardinality, prime keys and foreign keys. Use verbs to clarify your relationships. Show the attributes that are contained explicitly in the case. Create new attributes for identifiers etc only if absolutely required. Where you consider it necessary document assumptions. Acme Pizza’s delivery system services numerous franchised stores in the city. Retained for each store is the telephone number , address and store number. When a customer first calls in they are associated with the nearest outlet based on the first 3 digits of their phone#. A store may be the designated outlet for several different 3 digit codes. A customer’s phone#, name and address will also be recorded and retained. Employee information for each store is maintained and for each employee their name, employee id and job classification code .One of the employees is designated as the store manager. Each classification code has a standard description ( such as pizza chef, delivery driver, etc) and associated hourly rate. A daily work schedule for each employee is retained with start time and end time for each day. The schedule usually covers a 2 week period. Acme offers a number of standard pizzas. Each has is own description and a base price. A customer may add toppings. Each topping has its own code and description. as well as price . Not all toppings are available for all pizzas but many are available for more than one pizzas. This availability information needs to be accessible by the person processing the order so they can advise customers. Once a customer has placed an order for a pizza, the order date and time (30 minutes or its free), as well as the type of pizza and toppings are retained in the database. Active orders will be assigned to a driver who will call in the time the pizza was actually delivered for recording in the database. Using the attached data model write the SQL statements to satisfy the following queries //q1 List the names and ids of faculty teaching no sections in W02. Sequence the output in name order Select fname, fid from faculty Where sectionnbr not in (select sectionnbr from section Where semester = ‘w02’) Order by fname //q2 List the names of students that were enrolled in F01 courses taught by Moss Select sname from student