This Supplemental Project Book contains a collection of projects designed to allow students to apply the concepts described in the Database Design portion of the “ Database Design and Programming with SQL” course.
It includes five projects, each comprising an introduction, case study describing the project specifics, and steps to be followed during implementation, exercises, and examples. Projects may be completed in tandem with the completion of corresponding lessons for the duration of the course. The goal of the projects is to make a presentation of an entity relationship diagram based on the data acquired during the steps of the project. How to Use this Project Book The projects may be used at various stages of the course to allow students the opportunity to put into practice what they are learning during the curriculum.
Each project Introduction specifies approximately when the projects may be most applicable during the curriculum and approximately the duration for the projects. The Case Study section of the project book contains all supporting information related to the DJs on Demand and Global Fast Foods projects. The same section provides support questions for the remaining three interview-based projects, which are more advanced. This section includes the structural business rules, a few procedural business rules and other examples referencing concepts described in the Database Design course.
This information will be helpful when completing the exercises of the project. The practice Steps allow students to apply their understanding of entities and their attributes, recognize different relationships between entities, increase their understanding of business concepts (such as CRUD analysis and checking data integrity) and finally enhance their presentation skills. Each step is designed for completion at the end of a specific lesson in the course. Project Difficulty The projects are ranked from 1 to 5, 5 being the most difficult, to help teachers determine the best project(s) to use for their students.
The DJs on Demand project is rated 2 and provides a great deal of guidance. This project is recommended to reinforce the concepts students are just learning. Many examples are given in the curriculum. The Global Fast Foods project is rated 2. 5 and provides a great deal of guidance. This project is recommended to reinforce the concepts students are just learning on arcs, normalization, and many-to-many relationships. Oracle Academy Database Design iv Copyright © 2009, Oracle. All rights reserved. The Animal Shelter project is rated 3. and provides some guidance while encouraging practice of interviewing skills with the instructor, online research, and accurate and creative data modeling. The Natural Science Lab project is rated 4 and provides minimal guidance while encouraging practice of interviewing skills with a science teacher and accurate and creative data modeling. The Recycling Center project is rated 4 and provides minimal guidance while encouraging practice of interviewing skills with an outside person, and accurate and creative data modeling. Oracle Academy Database Design v Copyright © 2009, Oracle.
All rights reserved. DJs on Demand Project 1: 1. 1. DJs on Demand Introduction Difficulty Scale: 2 The DJs on Demand project describes the business scenario for a disc jockey music service. Project Use: Apply The project states the business rules to be basic concepts of considered before designing a database model. No database modeling interviews are required for this project. The goal of Application: Start this this project is to ensure that all students have the project following Section same information before preparing the final 2 Lesson 2. presentation and creating the ERD. Most other rojects contained in the Project Book will require students to work on their interviewing skills – since the goal of each database modeling project is to satisfy the clients’ needs. Project success is demonstrated with successful interviews, creative solution creation, and an effective presentation. Since this is an introductory project, most of the entities and relationships in the ERD for the DJs on Demand project are presented as examples in the Database Design lessons of the course. The tasks outlined in the practices and examples focus on problem recognition and methods for improving the example presented.
This project is complete when students have fully incorporated the final ERD into the presentation delivered to their instructor. The DJs on Demand project can be a useful tool for applying the basic concepts of the Database Design course. The lessons practices that reference this project are: • • • • • • Section 2 Lesson 2 Section 3 Lessons 1 and 2 Section 4 Lesson 2 Section 5 Lessons 3, 4 Section 7 Lessons 1 and 2 Section 15 Lessons 4 and 5 Note: If lessons are skipped, students may not be able to successfully complete the corresponding practice step, or any later steps in the project.
On a scale of 1-5, the difficulty rank for this project is 2. It is recommended that instructors teach this project first as it helps students solidify their understanding of the basic concepts of data modeling. The final ERD can be found in the Solutions section of this project. 1. 2. Case Study Read the complete scenario for the DJ business below. Oracle Academy Database Design 1 Copyright © 2009, Oracle. All rights reserved. DJs on Demand “ We started out as a group of friends who organized parties and customized our own music. Then we thought we’d turn it into a business to pursue our interests and earn some money.
We called ourselves the “ DJs on Demand. “ Everyone who works here is a partner. Every partner has a specific responsibility. The project manager makes the first contact with the client to discuss the event. Is it a birthday party, a wedding, an anniversary, a graduation? What is the date for the party or event? “ Once that’s decided, the event planner gets in touch with the client about specific locations, catering, decorations, and other specific details. The DJ talks with the client about the kind of music wanted. The project manager supervises the event planners and DJs.
He/she also authorizes expenditures related to a project. “ We have a large collection of CDs. Each CD contains several songs, and the same song can appear on several CDs. We like to classify each song by type (hip hop, salsa, R&B (rhythm and blues), techno, polka, rock, jazz, new age, classical, etc. ) “ We can propose an initial list of songs to the client depending on the event. Of course, a client can request other songs as well. “ Our client list is growing. We have a lot of repeat business – customers who like what we’ve done and ask us to work their other events.
We have some very busy customers who can have more than one event going on at the same time. “ We also have a list of themes that we can use to categorize these events. For example: a wedding may have a tropical theme, a party may have a carnival theme, an anniversary could have a sixties theme, etc. This helps us pick a venue and also gives us an idea of what the DJ (and other musicians) should wear. Some partners have a specialty or expertise – so a theme can also help us assign the right person to the job. “ Events are held either in a public space or a private home.
The event manager visits both and makes arrangements with the public-space renter or the private-home owner. “ Since several partners can work on an event, and an event can be assigned to several partners, we like to keep track of who is working on which event. We keep a log of what each event planner and DJ has done on an event, and when they did it. ” Below is the business scenario describing relationships. “ We like to classify all our music – each song or soundtrack – by type. The different types are rock, jazz, country, classical, pop, new age, etc.
We can add new types as the need arises – in fact we recently added a new type for rap music. We realize that a song can really be classified under more than one type, but for our purposes we select only one main classification type for each song. ” Oracle Academy Database Design 2 Copyright © 2009, Oracle. All rights reserved. DJs on Demand DJs on Demand clients, events, and themes are listed below. “ Our client list is growing. We have a lot of repeat business – customers who like what we’ve done who ask us to work for them again. We have some very busy customers who can have more than one event going on at the same time!
Each partner has some specialty or expertise – so when it’s appropriate, we like to classify our events by theme to help us assign the right person (partner) to the job. An event theme can be a beach party, medieval, carnival, retro sixties or seventies, etc. We keep adding event themes as we go. ” Procedural business rule examples are listed below. “ Initial contact with the client from DJs on Demand must be made by the project manager. “ Approval for travel requests to an event must be signed by the project manager for that event. ” 1. 3. Steps, Exercises, and Examples Step 1 – Recognizing attributes for an entity
This step may be undertaken following Section 2 Lesson 2. Three entities that play a role in a DJ business (SONG, EVENT, and CUSTOMER) are listed as the first three column headings of the table below. The fourth column contains a collection of attributes. Use a check mark to indicate if the attribute listed could be an attribute for the entities listed. (For example, could Title be an attribute for Song, for Event, and for Customer? ) Table 1: Entity and attribute assignment SONG EVENT CUSTOMER Attributes Title Description Venue First Name Phone Number Release date Last Name Type Email address
Step 2 – Understanding relationship between SONG and TYPE This step may be undertaken following Section 3 Lesson 1. You have learned about the relationship between SONG and TYPE. Answer the following questions: • Must every SONG have a TYPE? Oracle Academy Database Design 3 Copyright © 2009, Oracle. All rights reserved. DJs on Demand • • • • Can you have a SONG that doesn’t fall under any one TYPE? What would you do in this case? Must every TYPE describe or classify a SONG? Why would we want to have a TYPE with no songs under it? How many songs can fall under one type? Think about attributes for both of these entities.
Can you categorize the following music into playlists the same way SONGS are categorized into TYPES? Table 2: Musical listings SONG Viva la Vida Bach: The Cello Suites What a Wonderful World 4 Minutes Exclusive High and Dry We are the champions Help Me Brahms: The 4 Symphonies One more time Come on over Just my imagination The show must go on Thriller So what Mississippi Girl Back to black ARTIST Coldplay Yo-Yo Ma Louis Armstrong Madonna Chris Brown Radiohead Queen Elvis Presley Berliner Philarmoniker & Herbert von Karajan Daft Punk Shania Twain The Rolling Stones Pink Floyd Michael Jackson Miles Davis Faith Hill
Amy Winehouse GENRE Alternative Classical Jazz Pop R&B/Soul Alternative Rock Pop, Rock Classical Electronic Country Rock Rock Pop Jazz Country R&B/Soul Step 3 – Understanding relationship between clients, events, and types This step may be undertaken following Section 3 Lesson 2. You learned about entities and saw how the CLIENT, EVENT and THEME entities relate. Can you think of other entities for the DJs on Demand project? You can go back to the structural business rules described in the Case Study section of this project. Step 4 – Procedural business rules This step may be undertaken following Section 4 Lesson 2.
Oracle Academy Database Design 4 Copyright © 2009, Oracle. All rights reserved. DJs on Demand You are already familiar with the structural business rules presented in the Case Study section. Another important piece of information, important when effectively modeling a database, is represented by the procedural business rules. An example of a procedural business rule is described in lesson 2 of section 4: Initial contact with the client must be made by the project manager. How could you incorporate this constraint in your ERD? Can you think of other examples of procedural business rules?
Step 5 – Resolving many-to-many relationships This step may be undertaken following Section 5 Lesson 3. You already saw how to solve the M: M relationship between the PARTNER and EVENT entities through the JOB ASSIGNMENT intersection entity. Incorporate this in your ERD. How would you solve a M: M relationship between SONG and CD? What about the one between SONG and EVENT? What would be a good name for the intersection entities and what attributes would they have? Incorporate this step in your ERD. Step 6 – Understanding CRUD requirements This step may be undertaken following Section 5 Lesson 4.
Performing a CRUD analysis on the model you created so far for the DJs on Demand project is important. A CRUD analysis helps you to check the completeness and accuracy of the data model. Use the business rules presented in the Case Study section and create a table following the example below. Look for words and phrases that impact CRUD (we want to track data, we need to enter data). Is there an entity or attribute or relationship that allows a user of the DJs on Demand to create, retrieve, update, or delete? Hint: All entities need to have one create and retrieve function.
This means you need to have the business rules that points to entering data in the entity and viewing the data once it is in the database. Table 3 CRUD requirements example worksheet Entity EVENT ENTITY NAME Oracle Academy Business Rules Since several partners can work on an event, and an event can be assigned to several partners, CRUD Function? CREATE We like to keep track of who is working on which event. Specific business rule RETRIEVE Database Design 5 Copyright © 2009, Oracle. All rights reserved. DJs on Demand relating to the entity Table 4 CRUD examples “ Whenever we get a new customer, we take down asic information (name, address, email) and assign an ID. ” Create “ We’d like to print out a list of songs to be played at each event. ” Retrieve “ The event manager reserves the location and may do a site visit. Then she notes down the status and date of each job. ” Update “ A number of our customers were small companies that were hit hard by the recession. They went out of business. We removed them from our current records. ” Delete Step 7 – Mutually exclusive relationships This step may be undertaken following Section 7 Lesson 1. You have learned that you can represent mutually exclusive relationships through arcs.
Incorporate the exclusive OR relationship between EVENT and PRIVATE HOME and PUBLIC SPACE in your ERD. Can you think of another example using the same method of mutually exclusive relationship? Step 8 – Supertypes and subtypes This step may be undertaken following Section 7 Lesson 2. In lessons 1 and 2 of section 7, you learned about the two ways to represent supertypes and subtypes: as arcs or through recursive relationships. Think about the advantages and drawbacks of each model. Which way would you choose to incorporate the supertype PARTNER, followed by the subtypes EVENT PLANNER, DJ, MANAGER, and OTHER in your ERD?
Hint: Take into account the procedural business rule about the manager described above. Step 9 – Presenting the design to the instructor This step can be undertaken at the end of Section 15 Lesson 4. Create a presentation for the DJs on Demand Director, whose role will be played by your instructor. Organize your presentation, by including: • • Statement of the problem Information requirements of the business clearly stated Oracle Academy Database Design 6 Copyright © 2009, Oracle. All rights reserved. DJs on Demand • • Assumptions and constraints you took into account The ERD
You will present this to your instructor, and you will be given the opportunity to present the ERD as a communication tool, along with the business rules, to show the client that you understand their needs and that these needs are being met by your design. A suggested order for the presentation is a follows: 1. 2. 3. 4. 5. 6. 7. 8. Introduce the group members State the business issue that you addressed Present and explain the ERD (large enough for all to see) Summarize how your solution will meet the client’s needs Present written documentation State assumptions that you made in creating your solution Thank the clients for their time
Exit gracefully Remember: When you have a very large diagram, it may also help to break it up into smaller diagrams of functionally related entities. You could use the smaller sub-diagrams when presenting to different groups within the customer’s company. Hint: Review Section 11 Lesson 1 for drawing conventions for readability. Step 10 – Making modifications and new requirements This step can be undertaken at the end of Section 15 Lesson 5. Modify your ERD based on the feedback received from the presentation to the DJs on Demand Director. Produce a Design Revision Document outlining the changes made since the presentation was given.
Include the modified ERD with the Design Revision Document and submit the package to your instructor for review. Think of reports that can be generated from the future system, which includes the modifications you just made. Document how you imagine these reports could be used in the business. For example, the staffing manager could run an “ Events Report” that lists the names of the partners who worked on different projects. If it looks like some of the partners are busier and perform better, then, the manager can promote them. Step 11 – Checking data integrity in the DJ database
Examine the sample data for these tables in the DJs on Demand database. Check for entity, referential, and column integrity. Identify any data integrity violations. Assume that all date columns should have a date format and all cost columns should have a number format. Oracle Academy Database Design 7 Copyright © 2009, Oracle. All rights reserved. DJs on Demand Figure 1 Sample data for DJs on Demand database Primary Key refers to CLIENTS id 100 45 19 Primary Key Foreign Key id 15 17 25 50 first_name Antonio Mary Sarika Ivan EVENTS name Peter’s graduation Collins wedding Collins rehearsal dinner Eiks Club nnual fundraiser last_name Peters Collins Patel Balazs date phone_num 555-1891 662-2275 383-4572 777-5511 description Day before wedding 01-May[email protected]com[email protected]com Foreign Key 800. 00 22-Apr 12-Mar Email[email protected]com Cocktail hour followed by dinner Garden party Black-tie affair cost client _id 100 theme_ code BB 2gs 45 TROP 400. 00 45 TROP 1200. 0 0 77 SIXT refers to Primary Key THEMES code CARN SIXT TROP description Carnival Sixties Tropical Step 12 – The PARTNER supertype Transform the PARTNER supertype in the DJ model, using the supertype or single-table implementation. Oracle Academy
Database Design 8 Copyright © 2009, Oracle. All rights reserved. DJs on Demand 1. 4. Solutions Solution Step 1 – Recognizing attributes for an entity Answer: See Table 5 Entity and attribute assignment Table 5 Entity and attribute assignment SONG v v EVENT CUSTOMER v v v v v v v v v Attributes Title Description Venue First Name Phone Number Release date Last Name Type Email address Solution Step 2 – Understanding relationship between SONG and TYPE • Must every SONG have a TYPE? Answer: Yes. The relationship between SONG and TYPE is mandatory. • Can you have a SONG that doesn’t fall under any one TYPE?
What would you do in this case? Answer: Yes, it is possible. In this case, you would create another type, because chances are there are going to be more songs classified under that type. • Must every TYPE describe or classify a SONG? Why would we want to have a TYPE with no songs under it? Answer: No, it is not necessary. But it is helpful in case other songs will be included in the database that will have this type. • How many songs can fall under one type? Answer: The diagram and business scenario indicate that a TYPE may be used to classify several songs, or as many as needed for the database. Think about attributes for both of these entities. Answer: Answers will vary, but for SONG, some attributes (actually used in the ERD) are id, title, duration, and artist. For TYPE, attributes: code and description. Examples of playlists: • • • • 90’s music My top rated Recently added Recently played Oracle Academy Database Design 9 Copyright © 2009, Oracle. All rights reserved. DJs on Demand • • Party shuffle Rock (might include indie, alternative, hard rock) Students should now categorize the songs belonging to these playlists or others.
Solution Step 3 – Understanding relationship between clients, events, and types Answer: Other entities for the DJs on Demand project include: PARTNER, CLIENT, EVENT, JOB ASSIGNMENT, PRIVATE HOME, PUBLIC SPACE, THEME, TYPE, PLAY LIST ITEM, SONG, PACKAGE, TRACK LISTING, CD Solution Step 4 – Procedural business rules Answer: This is a rule that will have to be implemented by a computer program. Visualize a system where the partners can look up events and update them. The program could allow only the project manager to enter a new event and then assign the partners who will be working on it. Once the assignment s made, the system could send out an email notification to the event manager and the DJ, giving them details about the event, the customer contact information, etc. Other procedural business rules: • • • Only the project manager can make changes regarding the details of an event, after the client has requested them. Only the event manager can make arrangements for the venue of an event. All the advertisements for the business have to be approved by the manager. Solution Step 5 – Resolving many-to-many relationships Answer: The intersection entity for SONG and CD would be TRACK LISTING and could have an attribute called “* track”.
The intersection entity for PARTNER and EVENT would be JOB ASSIGNMENT and could have the following attributes: status and #date. Students should now incorporate these new entities in their preliminary ERD. Solution Step 6 – Understanding CRUD requirements Answer: Examples include: • • “ The project manager makes the first contact with the client to discuss the event. ” – CREATE “ Once that’s decided, the event planner gets in touch with the client about specific locations, catering, decorations, and other specific details. The DJ talks with the client about the kind of music wanted. ” – RETRIEVE Oracle Academy
Database Design 10 Copyright © 2009, Oracle. All rights reserved. DJs on Demand • • • • “ Of course, a client can request other songs as well” – UPDATE “ Since several partners can work on an event, and an event can be assigned to several partners, we like to keep track of who is working on which event. We keep a log of what each event planner and DJ has done on an event, and when they did it. ” – RETRIEVE “ We can add new types as the need arises – in fact, we recently added a new type for rap music. ” – UPDATE “ We keep adding event themes as we go” – UPDATE Solution Step 7 – Mutually exclusive relationships
Answer: Students should now incorporate these new relationships in their preliminary ERD. Check to see they understood why they are needed. When they are asked if they can think of another example using the same method of mutually exclusive relationships, answers will vary, but it would be good to introduce them to the different entities related to the PARTNER one, such as EVENT PLANNER, DJ, MANAGER and OTHER. Solution Step 8 – Supertypes and subtypes Answer: An advantage of the recursive relationships is that they can represent hierarchy in an ERD. Usually, supertypes/subtypes are used to represent classifications or types of things.
Arcs are used when you want to represent mutually exclusive relationships between entities. An M: 1 relationship would not be suited to a supertype/subtype construct. Also, arcs should be used when the exclusive relationships between the entities are more important to the business. The arc tells the reader of the diagram that only one of the “ features” of the main entity will have the relationship with the main entity for each instance. The entities EVENT PLANNER, DJ, and MANAGER have distinct sets of attributes, so there exists a strong need to model them as distinct entities.
When supertype/subtype is redrawn as an arc, the relationships in the arc are mandatory 1: 1 relationships. PRO Hierarchical: Hierarchical structures are more explicit and are easier for most people to understand because they are very similar to an organizational chart. Each entity can have its own mandatory attributes and relationships, if the business requires this (instead of all optional attributes and relationships, as you would have in a recursive). In this way, your data model truly reflects the business rules. PRO Recursive: Recursive relationships tend to be simpler because you are using only one entity.
Your diagram will be less “ busy”. However, they are less specific – you cannot have mandatory attributes or relationships unless they are mandatory in all instances of the entity. Oracle Academy Database Design 11 Copyright © 2009, Oracle. All rights reserved. DJs on Demand More: If the structure of the types of relationships changes often, a recursive relationship is easier to maintain. If the structure is fixed, then the more explicit hierarchy can be better. A structure that doesn’t change very much would be a building with suites and floors and rooms.
This is a good case for using an explicit hierarchy. On the other hand, a company that reorganizes frequently (going from a fairly flat organizational structure to one with many levels and vice versa) would probably be better modeled with a recursive relationship. Taking into account the procedural business rule that “ Initial contact with the client must be made by the project manager, and that managers, thus, should supervise the other PARTNERS”, the use of subtypes and the recursive relationship is preferred. The ERD makes it possible for managers to manage other managers.
Solution Step 9 – Presenting the design to the instructor Answer: Each presentation must contain the following: • • • • • • Statement of the Problem Statement of the Proposed Solution The information requirements of the business/organization clearly stated The business rules as they apply to the information requirements of the company/organization stated as single sentences The assumptions and constraints that had to be considered clearly stated ERD Oracle Academy Database Design 12 Copyright © 2009, Oracle. All rights reserved. DJs on Demand Figure 2 DJs on Demand entity relationship diagram
Solution Step 10 – Making modifications and new requirements Answer: Examples of reports: • • • List of the locations of the private homes used for parties so that the business knows where to advertise more List of the most interested clients, so that they can receive discounts List of the most played songs so that the DJs can make better recommendations/improve their play lists Solution Step 11 – Checking data integrity in the DJ database Table 6 Data integrity check CLIENTS table Oracle Academy Entity integrity: ID for Sarika Patel should not be null. Database Design 13
Copyright © 2009, Oracle. All rights reserved. DJs on Demand EVENTS table Column integrity: Cost for Collins wedding (event id = 17) is not numeric. Column integrity: Date for Collins rehearsal dinner (event id = 25) is not a valid date format. Referential integrity: There is no client 77 in the CLIENTS table (event id = 50). Referential integrity: There is no theme corresponding to “ BB” in the THEMES table (event id = 15). Solution Step 12 – The PARTNER supertype Answer: After going through the activity, you may want to conduct the following discussion to check for understanding: • • • • Remind students that they need a discriminator column. What would this column be named? (Answer: pnr_type. ) What are the allowable values for this column? (Answer: EPR, DJ, MNR. ) Remind students that they need a check constraint. What would it check for? (Answer: (pnr_type = ‘ EPR’ and expertise is not null and specialty is null and authorized_expense_limit is null) or (pnr_type = ‘ DJ’ and expertise is null and specialty is not null and authorized_expense_limit is null) or (pnr_type = ‘ MNR’ and expertise is null and specialty is null and authorized_expense_limit is not null). What does the foreign-key column pnr_id refer to? (Answer: the PARTNERS table. ) What relationship was it mapped from? (Answer: the recursive relationship in PARTNER. ) What does it represent? (Answer: the id of the partner’s manager. ) Oracle Academy Database Design 14 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Project 2: 2. 1. Global Fast Foods Introduction The Global Fast Foods project describes the business scenario of a small fast-food restaurant. The business need for the database is that the director wants to better keep track of the staff and he shifts, the orders and the food items. The director wants to see reports stating the busiest times of the day, the hardest worker, who takes the most orders, the most popular items, the customers who frequent the Global Fast Foods restaurant the most. Difficulty Scale: 2 Project Use: Apply basic concepts of the Database Design course Application: Start this project following Section 3 Lesson 3. Like the other projects, this project consists of a series of practice steps with each step building on the results of the previous step. Each step should be completed at the end of a specific lesson in the course.
Similar to the DJs on Demand project, Global Fast Foods also has the business rules already defined. At the end of this project, students will have revised ERDs highlighting important concepts in data modeling, such as supertypes/subtypes, relationship types, 1NF, arcs, hierarchies & recursive relationships, solving many-to-many relationships, and normalization. An important feature of the DJs on Demand project is Step 9 – Modeling historical data. This step allows students to test their knowledge about the UIDs of intersection entities and barred relationships.
This project will also allow students to develop their skills regarding drawing conventions for readability in an ERD, since an easy reading of the diagram is essential for a successful presentation. The project ends with steps requiring the students to develop a presentation of their data modeling solution and then modify this in compliance with the Global Fast Foods’ director’s feedback, whose role will be played by the instructor. The lessons practices that reference this project are: • • • • • Section 3 Lesson 3 Section 4 Lesson 1 Section 5 Lessons 1, 2 and 3 Section 6 Lessons 2 and 4
Section 7 Lessons 1, 2 and 3 Note: If lessons are skipped, students may not be able to successfully complete the corresponding practice step, or any later steps in the project. On a scale of 1 to 5, the difficulty of this project is 2. 5. Instructors should choose to complete this project in class when students would benefit from more practice regarding the application of basic concepts in data modeling, such as Oracle Academy Database Design 15 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods supertypes/subtypes, relationship types, 1NF, arcs, hierarchies & ecursive relationships. The final ERD can be found in the solutions section at the end of this document. 2. 2. Case Study The Global Fast Foods project has the following business scenarios. “ I own a small fast-food restaurant. We feature food items from all around the world – hence the name, Global Fast Foods. Some of our employees work at the counters and take orders. An order can consist of one or more food items. “ I’d like to track which of my employees work the hardest – who takes the most orders? I want to know what the busiest times of the day are, and what the busiest days of the week are.
I also want to find out which food items are the most popular. I have different kinds of employees on staff, but for all of them I need to know their first name, last name, age, and phone number. “ I mentioned that we have different kinds of employees and how for all of them I need to know basic contact information. Oh yes, every employee gets a salary. In addition to that, I need to know other things depending on what the employee responsibility is: • • • A cook normally has some kind of training – vocational school, selftaught, apprentice work, etc. I like to record that.
The order taker is paid overtime on top of the standard salary. So I record how much we pay by the hour for every extra hour worked. The manager is responsible for supervising all employees and has a budget for expenses and a target revenue for the restaurant that he/she is in charge of. “ That’s it for now. As we expand, I may hire other types of employees, but I’m not sure what they would be at this time. “ When a customer places an order with one of our staff, that order taker is responsible for seeing that order through – for making sure the chef gets it, for assembling it, and for collecting payment.
If the customer has changes or questions about that order, he/she must go through the person the order was placed with. The order taker cannot ask another staff member to take care of it. “ You were asking about the items that can appear on an order? Well, mostly it’s food items, but sometimes a customer can also purchase a frequent-diner card. This card entitles a customer to discounts at our restaurant. Also, if a customer buys this card, we can get information such as name and address. This way we can send the customer coupons and other promotional materials.
The other benefit to us is that we can now track which items the customer likes to order regularly. When a customer comes in and Oracle Academy Database Design 16 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods uses the card, we now have a record of the orders placed with that particular card. “ A customer can have one frequent-diner card, and each card is for only one customer. Only one frequent-diner card can be used to place an order. “ We have a variety of food items on our menu. Each order can be for multiple food items.
And, of course, a food item (a hamburger, for example) can appear on many orders. “ All employees on our staff are assigned to shifts. We currently maintain a morning and an afternoon shift, but we are considering adding an early evening shift. Currently, we just have a sign-in sheet for each shift. It keeps getting lost, and then it’s hard for me to allocate the workload properly. Several employees work on a single shift, and we do have employees who work consecutive shifts. It helps me to know which of my staff are overworked and which are underutilized, so I’d like to keep track of who is orking double shifts, who is not working enough shifts, etc. Also, if there’s a problem on a shift, I like to know immediately which employees were working during that time. “ We just started introducing a promotional menu. This menu features food items that are not available on the regular menu. It’s a way for us to test new items and to take advantage of seasonal events (holidays, etc. ) and commercial offerings (for example, when the movie “ King Kong” was out, we offered oversized Kong burgers). Sometimes we have a little gift associated with a promotional item.
For the Chinese New Year, we featured a mooncake on the promotional menu, and everyone who ordered it got a free toy dragon, because it was the year of the dragon. Each promotional menu has a name, such as Back to School or Summer Barbecue, and it has a start and end date. There is only one promotion in effect at any given time. “ Our regular menus contain those items that customers expect when they come to Global Fast Foods. Right now we have two types: a breakfast menu, available from 6 a. m. to 11 a. m. , and a lunch menu, available from 11: 01 a. m. until closing time.
We’re still considering a separate dinner menu, but we’ll have to test that first. “ The manager is responsible for supervising all employees and has a budget for expenses and a target revenue for the restaurant that he/she is in charge of. ” 2. 3. Steps, Exercises, and Examples Step 1 – Speaking ERDish and drawing relationships This step can be undertaken at the end of Section 3 Lesson 3. Read the following business rules for the Global Fast Foods project and think about entities and relationships: Oracle Academy Database Design 17 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods I own a small fast-food restaurant. We feature food items from all around the world – hence the name, Global Fast Foods. Some of our employees work at the counters and take orders. An order can consist of one or more food items. “ I’d like to track which of my employees work the hardest – who takes the most orders? I want to know what the busiest times of the day are, and what the busiest days of the week are. I also want to find out which food items are the most popular. I have different kinds of employees on staff, but for all of them I need to know their first name, last name, age, and phone number. It might be helpful to take a review slides in Section 3 Lesson 1 in order to better understand the relationship between customer and order. Step 2 – Supertypes and subtypes This step can be undertaken at the end of Section 4 Lesson 1. Read the additional interview notes for Global Fast Foods listed below. Revise the ERD to reflect this information. “ I mentioned that we have different kinds of employees and how for all of them I need to know first name, last name, age, and phone number. Oh yes, every employee gets a salary. In addition to that, I need to know other things depending on what the employee responsibility is: • • A cook normally has some kind of training – vocational school, selftaught, apprentice work, etc. I like to record that. The order taker is paid overtime on top of the standard salary. So I record how much we pay by the hour for every extra hour worked. The manager is responsible for supervising all employees and has a budget for expenses and a target revenue for the restaurant that he/she is in charge of. “ That’s it for now. As we expand, I may hire other types of employees, but I’m not sure what they would be at this time. ” Step 3 – Relationship transferability
This step can be undertaken at the end of Section 5 Lesson 1. Read the interview with the owner of Global Fast Foods and revise the ERD as necessary. “ When a CUSTOMER places an order with one of our STAFF, that ORDER TAKER is responsible for seeing that ORDER through – for making sure the chef gets it, for assembling it, and for collecting payment. If the CUSTOMER has changes or questions about that ORDER, he/she must go through the person the ORDER was placed with. The ORDER TAKER cannot ask another STAFF member to take care of it. ” Oracle Academy Database Design 18
Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Step 4 – Relationship types This step can be undertaken at the end of Section 5 Lesson 2. Read the following additional notes from a conversation with the Global Fast Foods owner and modify the ERD as needed: “ You were asking about the items that can appear on an order? Well, mostly it’s food items, but sometimes a customer can also purchase a frequent-diner card. This card entitles a customer to discounts at our restaurant. Also, if a customer buys this card, we can get information such as name and address.
This way we can send the customer coupons and other promotional materials. The other benefit to us is that we can now track which items the customer likes to order regularly. When a customer comes in and uses the card, we now have a record of the orders placed with that particular card. “ Each customer may have one frequent-diner card, and each card is for only one customer. Only one frequent-diner card can be used to place an order. “ We have a variety of food items on our menu. Each order can be for multiple food items. And, of course, a food item (a hamburger, for example) can appear on many orders. Step 5 – Resolving many-to-many relationships This step can be undertaken at the end of Section 5 Lesson 3. Resolve the many-to-many relationship between ORDER and FOOD ITEM. How do you track the quantity of each food item ordered? Step 6 – Normalization and first normal form This step can be undertaken at the end of Section 6 Lesson 2. Read the following notes from a subsequent interview with the owner of Global Fast Foods: “ All employees on our staff are assigned to shifts. We currently maintain a morning and an afternoon shift, but we are considering adding an early evening shift.
Currently we just have a sign-in sheet for each shift. It keeps getting lost, and then it’s hard for me to allocate the workload properly. Several employees work on a single shift, and we do have employees who work consecutive shifts. It helps me to know which of my staff are overworked and which are underutilized, so I’d like to keep track of who is working double shifts, who is not working enough shifts, etc. Also, if there’s a problem on a shift, I like to know immediately which employees were working during that time. ” Modify the ERD to include these new requirements. Oracle Academy
Database Design 19 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Go through each entity in your revised ERD and check that it is in first normal form. If not, modify the ERD so that it conforms to 1NF. Step 7 – Arcs This step can be undertaken at the end of Section 7 Lesson 1. Read the following interview notes with the Global Fast Foods owner. Refine the ERD accordingly. “ We just started introducing a promotional menu. This menu features food items that are not available on the regular menu. It’s a way for us to test new items and to take advantage of seasonal events (holidays, etc. and commercial offerings (for example, when the movie “ King Kong” was out, we offered oversized Kong burgers). Sometimes we have a little gift associated with a promotional item. For the Chinese New Year, we featured a mooncake on the promotional menu, and everyone who ordered it got a free toy dragon, because it was the year of the dragon. Each promotional menu has a name, such as Back to School or Summer Barbecue, and it has a start and end date. There is only one promotion in effect at any given time. Figure 3 Menu arcs “ Our regular menus contain those items that customers expect when they come to Global Fast Foods.
Right now we have two types: a breakfast menu, available from 6 a. m. to 11 a. m. , and a lunch menu, available from 11: 01 a. m. until closing time. We’re still considering a separate dinner menu, but we’ll have to test that first. ” Oracle Academy Database Design 20 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Redraw the REGULAR and PROMOTIONAL MENU entities as a supertype, and include the relationship to FOOD ITEM, based on the previously given scenario. Step 8 – Hierarchies and recursive relationships This step can be undertaken at the end of Section 7 Lesson 2.
The following was noted in an earlier interview with the owner of Global Fast Foods: “ The manager is responsible for supervising all employees and has a budget for expenses and a target revenue for the restaurant that he/she is in charge of. ” Modify the ERD to include a recursive relationship on STAFF showing the manager’s supervisory role. Step 9 – Modeling historical data This step can be undertaken at the end of Section 7 Lesson 3. Revisit the SHIFT ASSIGNMENT entity in the Global Fast Foods ERD, and recall an earlier interview from Step 6: “ All employees on our staff are assigned to shifts.
We currently maintain a morning and an afternoon shift, but we are considering adding an early evening shift. Currently we just have a sign- in sheet for each shift. It keeps getting lost, and then it’s hard for me to allocate the workload properly. Several employees work on a single shift, and we do have employees who work consecutive shifts. It helps me to know which of my staff are overworked and which are underutilized, so I’d like to keep track of who is working double shifts, who is not working enough shifts, etc. Also, if there’s a problem on a shift, I like to know immediately which employees were working during that time. Notice that “ consecutive shifts” means that an employee can work two shifts on the same date. Answer the following questions: 1. What is the UID of this entity? How is it represented on the diagram? 2. Explain what would happen if the UID of SHIFT ASSIGNMENT included only the barred relationships to STAFF and SHIFT, and not the date. 3. Explain what would happen if the UID of SHIFT ASSIGNMENT included a barred relationship to STAFF, and the date? 4. Explain what would happen if the UID of SHIFT ASSIGNMENT included a barred relationship to SHIFT, and the date.
Step 10 – Drawing conventions for readability This step can be undertaken at the end of Section 11 Lesson 1. Review your Global Fast Foods data model so far. Oracle Academy Database Design 21 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods 1. Identify the high-volume entities and redraw your ERD to use the “ crows fly south and east” convention as far as possible. Try to avoid criss-crossing lines, and make good use of white space. 2. Create a smaller diagram that contains all the entities that would be of interest to the order taker.
Step 11 – Basic mapping: the transformation process This step can be undertaken at the end of Section 12 Lesson 2. Transform the following entities in Global Fast Foods into table diagrams. Use suitable naming conventions. Figure 4 Entities to table diagrams Refer to the completed Global Fast Foods model and map the following: entities: • • • FOOD ITEM ORDER ORDER LINE Transform relationships into foreign-key columns. Use as many rows as necessary in the following table diagram. Table 7 Primary, foreign, and unique key mapping Key Type (pk, fk, uk) Optionality (“*” or “ o”) Column Name
Transform the STAFF supertype in the Global Fast Foods model, using the subtype or two-table implementation. Step 12 – Presenting the design to the instructor This step can be undertaken at the end of Section 15 Lesson 4. Oracle Academy Database Design 22 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Create a presentation for the Global Fast Foods client, whose role will be played by your instructor. Organize your presentation, by including: • • • • Statement of the problem Information requirements of the business clearly stated Assumptions and constraints you took into account ERD
One example of an assumption is that there is only one Global Fast Foods restaurant. The current model does not consider a chain of restaurants. You will present this to your instructor, and you will be given the opportunity to present the ERD as a communication tool, along with the business rules, to show the client that you understand their needs and that these needs are being met by your design. A suggested order for the presentation is a follows: 1. 2. 3. 4. 5. 6. 7. 8. Introduce the group members State the business issue that you addressed Present and explain the ERD (large enough for all to see)
Summarize how your solution will meet the client’s needs Present written documentation State assumptions that you made in creating your solution Thank the clients for their time Exit gracefully Step 13 – Modifications and new requirements This step can be undertaken at the end of Section 15 Lesson 5. Modify your ERD based on the feedback received from the presentation to the Global Fast Foods client. Produce a Design Revision Document outlining the changes made since the presentation was given. Include the modified ERD with the Design Revision Document and submit the package to your instructor for review.
Think of reports that can be generated from the future system, which includes the modifications you just made. Document how you imagine these reports could be used in the business. 2. 4. Solutions Solution Step 1 – Speaking ERDish and drawing relationships Answer: Read the interview notes for Global Fast Foods and watch the video clips. Construct the ERD. Then present it to the class. Oracle Academy Database Design 23 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Solution Step 2 – Supertypes and subtypes Answer: See example below. Do not try to model “ manager supervising all employees” yet.
This is a hierarchical or recursive relationship (between an entity and itself) and will be covered in a later step. Figure 5 ORDER and ORDER TAKER relationship Solution Step 3 – Relationship transferability Answer: An order may be only taken by an order taker. Therefore, the diamond goes on the ORDER side of the relationship with the ORDER TAKER. Solution Step 4 – Relationship types Answer: Point out how the scenario clarifies the M: M relationship between FOOD ITEM and ORDER. We also add a new entity called FREQUENT DINER CARD. There is no need to model CUSTOMER because we record customer information on the card.
Someone may also ask, “ What if one customer places an order for a group of people, but each person in the group has a frequent-diner card? ” In this case, the discount applies to the total order, and the order gets tracked under the one frequent-diner card. Some students may not model a MENU entity, and some may decide that it is necessary. Both are valid. It is not clear right now that there is more than one menu. When we discuss arcs, they will get additional information that there are two menus. Oracle Academy Database Design 24 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods
Figure 6 Frequent diner card and menu entities Solution Step 5 – Resolving many-to-many relationships Answer: See figure below. Figure 7 ORDER and FOOD ITEM relationship Oracle Academy Database Design 25 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Solution Step 6 – Normalization and first normal form Answer: Go through each entity in your revised ERD and check that it is in first normal form. If not, modify the ERD so that it conforms to 1NF. The SHIFT ASSIGNMENT entity will result from the resolution of the M: M relationship between SHIFT and EMPLOYEE. There needs to be a date attribute in SHIFT ASSIGNMENT.
The UID should include barred relationships from the two originating entities, plus the date. However, we have not learned to model change over time yet. Point out the UID but no need to emphasize this yet. If some students have trouble understanding it, tell them that we will talk about modeling time later. Figure 8 Shift assignment entity Solution Step 7 – Arcs Answer: See figure below. One acceptable solution would be to have three entities in the arc: • • • PROMOTIONAL MENU BREAKFAST MENU LUNCH MENU BREAKFAST MENU and LUNCH MENU would have the same attributes code and hours served.
One acceptable solution would be to have four subtypes of MENU (BREAKFAST, LUNCH, PROMOTIONAL, OTHER). The type attribute would not be needed in the BREAKFAST and LUNCH subtypes. Oracle Academy Database Design 26 Copyright © 2009, Oracle. All rights reserved. Global Fast Foods Solution Step 8 – Hierarchies and recursive relationships Answer: Point out that the relationship is from MANAGER to STAFF, not STAFF to itself. This is because only a STAFF member who is a manager can supervise other employees. Also note that this ERD does not prevent a manager from supervising other managers.
A hierarchical relationship (with MANAGER as a separate entity) would make this clear. Figure 9 STAFF recursive relationship Solution Step 9 – Modeling historical data 1. What is the UID of this entity? How is it represented on the diagram? Answer: The UID of SHIFT ASSIGNMENT is a combination of STAFF id, SHIFT code, and SHIFT ASSIGNMENT date. This is represented by the barred relationships to STAFF and SHIFT, and the # before date. 2. Explain what would happen if the UID of SHIFT ASSIGNMENT included only the barred relationships to STAFF and SHIFT, and not the date.
Answer: Using just the barred relationships, without the date as part of the UID, an employee could never work the same shift again, even on a later date. 3. Explain what would happen if the UID of SHIFT ASSIGNMENT included a barred relationship to STAFF, and the date? Answer: Creating a barred relationship between SHIFT ASSIGNMENT and STAFF, and including the date in the UID, would mean that an employee could not work more than one shift on a given date. This would prevent an employee from working consecutive shifts. Oracle Academy Database Design 27 Copyright © 2009, Oracle. All rights reserved.
Global Fast Foods 4. Explain what would happen if the UID of SHIFT ASSIGNMENT included a barred relationship to SHIFT, and the date. Answer: Creating a barred relationship between SHIFT ASSIGNMENT and SHIFT, and including the date in the UID, would mean that only one employee could work on a particular shift on a given date. Solution Step 10 – Drawing conventions for readability Answer: The Global Foods ERD can be drawn in several ways that are clear and readable. Look for adherence to basic conventions such as no crossing relationship lines, relationship names on either side of the relationship
