Description
Resources to be read and analyzed before you work in this homework assignment. a. Lecture Notes 01 to 08 b. Special attention must be given to the “Oracle Datamodeling” Notes Lessons 1 to 10. c. Our textbook Chapter 1 to 7 You are provided with the problem statement, extensive business rules presentation and EER diagram provided by the authors for the textbook exercise 3.1 in Appendix I Your task for this assigned is described below. Part I (10 points) a. Review the problem 3.1 statement and the textbook author’s EER diagram. Read the textbook to understand the authors EER diagram and extract suggested entities, relationships, and business rules. Most importantly extract every business rule to be included in your own database conceptual design for which you will use the Oracle Datamodeler. b. Describe the organization and the purpose of the database to be designed c. Using the Oracle Datamodeler, build a dataflow diagram for the “Pizza Sales Report System”. d. Identify and list the entity sets e. Identify the list of attributes and the ID for each entity set (this will be the possible natural key). f. Identify and list the business rules associate with each entity set g. Identify and list the relationships among the entity sets and indicate if they are on-to-one, oneto-many, or many-to-many relationships. h. For each entity set identify a surrogate key or artificial key, if need it. This will be the Primary Key on the relational diagram and the ID identified as the NATURAL KEY. Both, the Primary key and the natural key need to be given the UNIQUE and NOT NULL constraints. These are the basic properties of the Primary and candidate Key. i. Using the Oracle Datamodeler build the EER diagram (database conceptual design). All business rules identified above must be entered into the Oracle Data Modeler. j. Proceed to convert the EER diagram into a Relational Diagram. Make sure all the constraints, naming convention, etc. have been followed in your Data Model. k. Review the Relational diagram and identify missing item or corrections that are needed in the data model. For example, missing entities, relationships, missing constraints, etc. If corrections are needed, make the corrections on the EER diagram and re-generate the relational diagram. l. Once you have finalized the EER diagram, proceed to generate the DDL m. Create a script similar the sample database schema “Create_HR_Database_Schema.sql” to create the database schema for this assignment using the DDL created in the above step. This may require making updates in the Oracle Datamodeler to make sure all the needed REFERENTIAL INTEGRITY CONSTRAINTS are created (e.g. Primary Keys, UNIQUE Keys, Referential Integrity, NULL, NOT NULL, DEFAULT). n. Using the Oracle SQL Developer, create an Oracle database userid “__HWK2” in the Oracle database “db1”. For example: fernandez_rolando_HWK2 o. Run the script that creates the database schema into your userid created above. p. Create INSERT statement to insert at least 8 rows in each table. Make sure that you need to load the parent tables first to avoid errors. q. Create at least 5 select statements to display data from the database schema. r. Create at least 5 delete and 5 update SQL statements. s. Build a PL/SQL procedure that creates a “Hungry Customers Report”. The reports contains the name, address, date of sale, pizza type, cost, and at the end the total costs. NOTE: the number of entities in your design could be larger that in the solution below. Part II: (10 Points) a. Create an Oracle RDS Instance b. Connect to the Oracle RDS Database Schema to Execute the steps from Part I to create the database schema in your new Oracle RDS instance, connect to the Oracle RDS using the Oracle SQL Developer, create the Oracle database schema using the DDL built for Part I. Deliverables: a. Submit a detailed report of Part I. This is a College level report with introduction, sections, and Summary b. Submit a detailed report of Part II: This should be in a tutorial format. Write a tutorial on how to complete Part II for a person new in Amazon Web Services (AWS) Appendix I Question 3.3 One of your (hipster) acquaintances thinks he has the next billion-dollar start-up idea for an app: Pizza Delivery with Entertainment. He heard from other people that you are following the course on database management, and asks you to design the EER model. Afterwards, he will use the EER model to ask programmers to implement the app. He explains the basic functionality of the app as follows: customers can order pizzas from restaurants to get delivered to a specific address, and if they want to, they can choose a special “entertainment order.” The following is a detailed explanation of the range of capabilities of the app. When people create an account for the app and become app users, they have to indicate their birthday and fill in their name and address. Every user should also be uniquely identifiable. Once the account is created, the users should be presented with three options. The first option in the app is to select “business owner.” We also ask these business owners to provide their LinkedIn account so we can add them to our professional network. Each business owner can own a number of pizza restaurants. Of these pizza restaurants, we want to register the zip code, address, phone number, website, and the opening hours. Each pizza restaurant can offer a number of pizzas. Of those pizzas, we want to keep the name (e.g., margherita, quattro stagioni), the crust structure (for example, classic Italian crust, deep dish crust, cheese crust), and the price. While two pizzas from different pizza restaurants may have the same name, they will not be exactly the same, as the taste will be different, and thus should be considered unique. Moreover, pizzas should be distinguishable even if they have the same price, e.g., a pizza margherita from Pizza Pronto in New York that costs €12 must be distinguishable from a pizza margherita from Pizza Rapido in Singapore, which also costs €12. The second option in the app is to select “hungry customer.” Of those hungry customers, we need a delivery address. Hungry customers can make orders for pizzas. Each order gets assigned an ID, and we want our app to log the date and time when the order was placed. We also allow the hungry customer to indicate the latest time of delivery, and ask how many people the order is for. An order can be for one or more pizzas. A special type of order can be made: the entertainment order. When an order is an entertainment order, the delivery person stays with the customer after delivering the pizza and entertains the customers (e.g., singing, making jokes, doing magic tricks) for a certain amount of time. When a hungry customer indicates that he or she wants to be entertained while eating the pizza, we not only want to register all the regular order information, but also the type of entertainment the user requests, and for how long (a duration). The third option in the app is that of “entertainer.” When users select entertainer, they need to provide a stage name, write a short bio about themselves, and indicate their price per 30 minutes. Every entertainment order is fulfilled by exactly one entertainer. Every entertainer can choose for which pizza restaurant(s) he or she wants to work. For each pizza restaurant an entertainer wants to work with, he or she should indicate his or her availability by day (Monday, Tuesday, Wednesday, etc.). Make an EER model to model the data requirements. Suggested EER Diagram follows. Please do not attempt to convert this EER diagram item by item to the Oracle Datamodeler. Use it to extract business required entity sets, business rules, relationships and types of relationships. In other words, your solution is not a translation of this EER diagram/ Appendix II Installing the Oracle Datamodeler. Go to: SQL Developer Datamodeler http://www.oracle.com/technetwork/developer-tools/datamodeler/downloads/index.html You will be prompted for your userid and password. If you do not have an Oracle userid, please create one. It is free. In Windows: A Zipped file will be downloaded Unzip the file and the “datamodeler” directory will be created. Double click on the “datamodeler.exe” to execute.
