Needing help with this project anything is appreciated. The model with the relationships is throwing me through a loop. Thanks
Database Project Part 1: 150 points Due: See Canvas for Due Dates Objective: This assignment is part 1 of 3 that will lead you through the database development process for a specific application. This portion of the project focuses on conceptual data modeling. In the ANSI/SPARC 3-schema model of databases, the external schema reflects the view of the data held by an individual user, application, report, form, etc. For this project, you will develop E-R diagrams in sub-views reflecting the data requirements, and then integrate them into a single conceptual schema (the Logical Model) that supports the entire application. All E-R diagrams for this model will be contained in a single Data Modeler file using Sub-Views to define the external models. PLEASE READ THIS ENTIRE DOCUMENT PRIOR TO BEGINNING THIS ASSIGNMENT. This assignment assumes you have completed the first SQL Data Modeler Tutorial. Description of data modeling scenario Create a database model to support the Anderson's auto sales, a used car sales business. This is the same business used for Homework Assignment 2 in this course. A detailed description of the requirements for the database is provided below. See the grading rubric posted to Canvas with this assignment for grading details. A list of attributes captured for Homework Assignment 2 is posted with this assignment for your reference, Note 1: Application software will NOT be developed for this assignment, and no user interfaces, reports, calculations, or business logic will be written. Focus only on the data and information requirements. In other words, you will be creating the database that supports the application not the application itself. Note 2: In many cases, value-lists are provided for attributes for clarity. Do not model values, or domains, for Part 1 of the project. Part 2 of the project will include defining value-lists, or Domains, for the database attributes. The Oracle SQL Data Modeler data modeling tool is required for this project. TASK 1: External Schemas (5 Sub-view/External Schemas) 125 Points (25 points per Sub-view/External Schema) Create a new Data Modeler file and give it a meaningful name (i.e. AndersonsCars). Develop an E-R Diagram with Sub-view/External Schemas as detailed on the following pages of this document using Information Engineering notation. You will need a minimum of five (5) logical Sub-view/External Schemas in your model as described on the following pages. The 'Logical Model in the Data Modeler will represent your Conceptual Schema for your design. Set the Notation to Information Engineering for your Logical model and Sub-view/External Schemas Set your Data Modeler preferences as defined in the Data Modeler Tutorial #1 before beginning this part of the project. . See the Data Modeler Tutorial #1 posted to Canvas for details on how to create Sub-view. The same entity may appear in more than one Sub-view/External Schema in your model. Attributes added in one Sub-view will appear in all Sub-views containing the shared entity. To add an existing entity to a Sub-view: Expand the Logical Model in the Navigator pane; then expand the Entities; then drag- and-drop the required entities into the Sub-view. DO NOT ATTEMPT TO CREATE MULTIPLE ENTITIES WITH THE SAME NAME. FAILURE TO CREATE THE REQUIRED DATA MODELER Sub-view/External Schemas WILL RESULT IN AN AUTOMATIC 20% REWORK PENALTY. THE FILES WILL BE RETURNED TO YOU TO CREATE THEM. Task 1 - 1: Sub-view/External Schema 1: Customer Registration Customers may register for an account using Anderson's website. Customers may also save their favorite cars (also called 'Saved Cars'. See the 3 screenshots below for the fields required for customer accounts and customer favorites. Customers may sign up for availability and price-change alerts on their saved cars. We will NOT store images as part of this assignment. DO NOT INCLUDE MAKING PAYMENTS OR MANAGING A FINANCE ACCOUNT AS PART OF THIS ASSIGNMENT. Screenshot 1: | Register | MyCarMax + carmax.com/mycarmax/register W. CARmax SECURE Why create a MyCarMax account? First name Last name Access saved cars and searches Email Compare your favorites Set up new inventory alerts Password Include a minimum of a characters and at least one number and one letter, No Spaces, please. Make car payments and manage finance account 4 Conhrm password REGISTER FEEDBACK Already have an account? Sign-In to your account Processing request... llor rent information to third parties Screenshot 2: Screenshot 2: Edit Your Profile Info First Name Sandra Last Name Vlasnik Address Optional) Zip (Optional) State (Optional) City (Optional) Email djangoappsandy@gmail.com Phone Number UPDATE PROFILE Communication Preference Let us know the best way to contact you. O No Preference O Email O Phone UPDATE PREFERENCES Continued next page Screenshot 3: One example of a saved/favorite car: Saved Cars Saved Searches Appointments Profile 1 Saved Car 15 2018 Lexus RC 350 F-SPORT $43,998* 12K Fort Worth $199 Transfer Fee to Omaha SIC Availability and price alert emails (402) 289-6930 Free Vehicle History Report Additional Business Rules: 1. Each customer will have one unique e-mail address 2. Customers may save many cars as favorites, also called 'Saved Cars' 3. Cars may be saved as favorites by many customers. 4. Customers may sign up for alert e-mails on many cars and cars may have many alerts requested for them. 5. Customers may set alerts based on Make, Model, Start and End Years, and Low and High Milage Task 1 2: Sub-view/External Schema 2: Shopping (Browsing) for a Car and Car Details Customers must be able to browse for available cars. Each car has multiple attributes that are searchable via the website. The following 1 screenshot shows 3 of the attributes that must be stored for each car. The business rules below for additional required attributes. We will NOT save favorite searches for the customers of this business: Screenshot 1: Screenshot 1: Shop by type O SUVS Trucks Crossovers Sedans Coupes Shop by brand Ford NISSAN Jeep TOYOTA CHEVROLET HONDA SEE ALL BRANDS DOO LES GMC Mercedes HYUNDAI Shop by price Under $10.000 > Under $15,000 -> $99 Under $20,000 $20,000-$25,000 > $25,000-$30,000 $30,000 and above C. Additional Requirements and Business Rules: Reminder The value-lists provided are for clarity and will be used in Part 2 of the project to define Domains for the attributes. 1. Each car has one price that must be a positive value. 2. Cars may be as old as 1900 and be any year greater than 1900. 3. Each car will also include the following attributes: a. Vehicle Identification Number (VIN), unique to each car. b. Make Model d. Mileage e. Type f. Body Style g. Exterior Color h. Interior Color i. Transmission - values include: Automatic or Manual j. Drivetrain values include: 2WD, 4WD, AWD, RWD 4. Each car may have many special Features and each Feature may be included on many cars. a. See the Homework 2 solution example for potential values. 5. Each car may have multiple warranties and each warranty may be used on many cars. Warranty values may include the number of years, the number of months, and/or the number of maximum miles covered. 6. Anderson's dealership is local to Omaha only, so location information for vehicles will not be tracked in this database 7. The Original cost of the vehicle paid by Anderson and the date the car was purchased must also be stored. 8. Additional features may be implemented based on the Homework 2 solution file posted to this assignment. Task 1 3: Sub-view/External Schema 3: Customer Appointments to View a Car and Purchase Orders: Task 1 - 3: Sub-view/External Schema 3: Customer Appointments to View a Car and Purchase Orders: If a customer is interested in purchasing a car, the customer will request an appointment to view the car, and a sales rep will contact them. See the screen shot below for the appointment request form. 1. The appointment request will include the specific car the customer is interested in viewing, the requested date and time of the appointment, the customer requesting the appointment, and whether the customer would like to discuss selling their car and/or financing options at their appointment. 2. If a customer chooses to purchase the car at their appointment, a purchase order will be created. The customer information, the car information, the date of the purchase, the date of delivery of the car, the sales-rep selling the car, and the purchase price of the car will be recorded and saved for a purchase order. 3. Finance information will NOT be stored for this assignment. Screenshot 1: Request Select a day and time for your test drive at CarMax Town Center in Town Center, GA Date Time Phone Number During your visit you can also (Optional) I Get an offer on your current car O Explore financing By clicking "Submit and providing your phone number, you agree that Car Max may call or text you (including via autodialer or prerecorded call) at the number you provide for marketing sales purposes, information relating to your appointments) or any other servicing or informational purpose. You do not need to consent to receive calls/texts to purchase from or sell to CarMax Msg & datarates may apply. Msg freq. varies. Text HELP for help. Text STOP to cancel. Texting Terms Privacy Policy SUBMIT Additional Business Rules: 1. Each customer may request many appointments, but each appointment is for a single customer. 2. Each customer may purchase many cars, but each purchase order is for a single customer. 3. Each salesperson may attend many appointments, but each appointment is assigned to a single salesperson. 4. Each appointment is for a single car, but each car can be included in many appointments. 5. Each car many appear in a single purchase order, and each purchase order is for a single car. Task 1 - 4: Sub-view/External Schema 4: Salesperson For each salesperson, the database must store the following information: 1. Name (first and last), Address (Street, Street 2, City, State, Zip code), e-mail, hire date, username, password (encrypted), cell phone number, office phone number 2. Note there is no screenshot for this requirement. Task 15: Sub-view/External Schema 5: Customer Appraisal Appointment Customers may sell their cars to Anderson's car dealership. If an appraisal appointment is requested by a customer, the customer must provide the year, make, model, and mileage of their car. See the screenshot below for the appraisal appointment request. If a customer chooses to sell their car to Anderson's, a sales order will be created. The customer information, the information about the car, the date of the sale, the date of delivery of the purchased car, the sales-rep approving the purchase, and the purchase price of the car will be recorded for the purchase order. Note that when a purchase of a customer's car is completed, the car becomes part of the inventory, and information about the car is saved with all other vehicles owned by Anderson's. See Sub-view/External Schema 1 above for data requirements for a car. Screenshot 1. Screenshot 1: We buy all kinds of cars Year Make Model Mileage SEE ABOUT YOURS Additional Business Rules: 1. Each appraisal appointment is for a single customer, and a single salesperson attends the appraisal meeting. 2. Both customers and salespersons may attend many appraisal meetings. 3. Each purchase order is for a single customer and a single vehicle. 4. Each customer may request many appraisal appointments and may sell many cars to Anderson's. 5. If the car is purchased by Anderson, the purchase price and date will be stored. TASK 2: Conceptual Schema (Logical Model) 25 Points After all Sub-view/External Schemas are created, return to the Logical Model screen and organize the entities and relationships until all items are visible. Ensure all data requirements are supported by your model and all relationships are defined AND LABELED with verb-phrases correctly. To receive full credit, the Logical Model and all Sub-views must use Information Engineering Notation. Data Modeler Hints: 1) To organize the entities on the Logical Model screen, right-click on the background of the Logical Model, select 'Layout' `Auto Layout' Choose a Layout (1, 2, 3, or 4) to have the software reorganize the layout for better viewing. 2) Right-click a relationship line and select 'add elbow' to allow the relationship line to 'bend'. 3) Right click on the background of the Logical model or any of the Sub-view/External Schema model screens and select 'Show' then 'Labels' to view your verb phrases/relationship names. 4) Reminder: If you created any super-type/sub-type entities, the primary key of a sub-type entity is the same as the primary key of the super-type, even though it is not visible in the Data Modeler model. Submitting this Assignment on Canvas: Create an Archive/Zip file inlcude the model file (*.dmd file), AND the associated directory/folder created by the Data Modeler software into a single file, then, post this zipped file to the Canvas Assignment link. Do NOT move the .dmd file inside the folder with the same name before archiving/zipping the files for submission. Database Project Part 1: 150 points Due: See Canvas for Due Dates Objective: This assignment is part 1 of 3 that will lead you through the database development process for a specific application. This portion of the project focuses on conceptual data modeling. In the ANSI/SPARC 3-schema model of databases, the external schema reflects the view of the data held by an individual user, application, report, form, etc. For this project, you will develop E-R diagrams in sub-views reflecting the data requirements, and then integrate them into a single conceptual schema (the Logical Model) that supports the entire application. All E-R diagrams for this model will be contained in a single Data Modeler file using Sub-Views to define the external models. PLEASE READ THIS ENTIRE DOCUMENT PRIOR TO BEGINNING THIS ASSIGNMENT. This assignment assumes you have completed the first SQL Data Modeler Tutorial. Description of data modeling scenario Create a database model to support the Anderson's auto sales, a used car sales business. This is the same business used for Homework Assignment 2 in this course. A detailed description of the requirements for the database is provided below. See the grading rubric posted to Canvas with this assignment for grading details. A list of attributes captured for Homework Assignment 2 is posted with this assignment for your reference, Note 1: Application software will NOT be developed for this assignment, and no user interfaces, reports, calculations, or business logic will be written. Focus only on the data and information requirements. In other words, you will be creating the database that supports the application not the application itself. Note 2: In many cases, value-lists are provided for attributes for clarity. Do not model values, or domains, for Part 1 of the project. Part 2 of the project will include defining value-lists, or Domains, for the database attributes. The Oracle SQL Data Modeler data modeling tool is required for this project. TASK 1: External Schemas (5 Sub-view/External Schemas) 125 Points (25 points per Sub-view/External Schema) Create a new Data Modeler file and give it a meaningful name (i.e. AndersonsCars). Develop an E-R Diagram with Sub-view/External Schemas as detailed on the following pages of this document using Information Engineering notation. You will need a minimum of five (5) logical Sub-view/External Schemas in your model as described on the following pages. The 'Logical Model in the Data Modeler will represent your Conceptual Schema for your design. Set the Notation to Information Engineering for your Logical model and Sub-view/External Schemas Set your Data Modeler preferences as defined in the Data Modeler Tutorial #1 before beginning this part of the project. . See the Data Modeler Tutorial #1 posted to Canvas for details on how to create Sub-view. The same entity may appear in more than one Sub-view/External Schema in your model. Attributes added in one Sub-view will appear in all Sub-views containing the shared entity. To add an existing entity to a Sub-view: Expand the Logical Model in the Navigator pane; then expand the Entities; then drag- and-drop the required entities into the Sub-view. DO NOT ATTEMPT TO CREATE MULTIPLE ENTITIES WITH THE SAME NAME. FAILURE TO CREATE THE REQUIRED DATA MODELER Sub-view/External Schemas WILL RESULT IN AN AUTOMATIC 20% REWORK PENALTY. THE FILES WILL BE RETURNED TO YOU TO CREATE THEM. Task 1 - 1: Sub-view/External Schema 1: Customer Registration Customers may register for an account using Anderson's website. Customers may also save their favorite cars (also called 'Saved Cars'. See the 3 screenshots below for the fields required for customer accounts and customer favorites. Customers may sign up for availability and price-change alerts on their saved cars. We will NOT store images as part of this assignment. DO NOT INCLUDE MAKING PAYMENTS OR MANAGING A FINANCE ACCOUNT AS PART OF THIS ASSIGNMENT. Screenshot 1: | Register | MyCarMax + carmax.com/mycarmax/register W. CARmax SECURE Why create a MyCarMax account? First name Last name Access saved cars and searches Email Compare your favorites Set up new inventory alerts Password Include a minimum of a characters and at least one number and one letter, No Spaces, please. Make car payments and manage finance account 4 Conhrm password REGISTER FEEDBACK Already have an account? Sign-In to your account Processing request... llor rent information to third parties Screenshot 2: Screenshot 2: Edit Your Profile Info First Name Sandra Last Name Vlasnik Address Optional) Zip (Optional) State (Optional) City (Optional) Email djangoappsandy@gmail.com Phone Number UPDATE PROFILE Communication Preference Let us know the best way to contact you. O No Preference O Email O Phone UPDATE PREFERENCES Continued next page Screenshot 3: One example of a saved/favorite car: Saved Cars Saved Searches Appointments Profile 1 Saved Car 15 2018 Lexus RC 350 F-SPORT $43,998* 12K Fort Worth $199 Transfer Fee to Omaha SIC Availability and price alert emails (402) 289-6930 Free Vehicle History Report Additional Business Rules: 1. Each customer will have one unique e-mail address 2. Customers may save many cars as favorites, also called 'Saved Cars' 3. Cars may be saved as favorites by many customers. 4. Customers may sign up for alert e-mails on many cars and cars may have many alerts requested for them. 5. Customers may set alerts based on Make, Model, Start and End Years, and Low and High Milage Task 1 2: Sub-view/External Schema 2: Shopping (Browsing) for a Car and Car Details Customers must be able to browse for available cars. Each car has multiple attributes that are searchable via the website. The following 1 screenshot shows 3 of the attributes that must be stored for each car. The business rules below for additional required attributes. We will NOT save favorite searches for the customers of this business: Screenshot 1: Screenshot 1: Shop by type O SUVS Trucks Crossovers Sedans Coupes Shop by brand Ford NISSAN Jeep TOYOTA CHEVROLET HONDA SEE ALL BRANDS DOO LES GMC Mercedes HYUNDAI Shop by price Under $10.000 > Under $15,000 -> $99 Under $20,000 $20,000-$25,000 > $25,000-$30,000 $30,000 and above C. Additional Requirements and Business Rules: Reminder The value-lists provided are for clarity and will be used in Part 2 of the project to define Domains for the attributes. 1. Each car has one price that must be a positive value. 2. Cars may be as old as 1900 and be any year greater than 1900. 3. Each car will also include the following attributes: a. Vehicle Identification Number (VIN), unique to each car. b. Make Model d. Mileage e. Type f. Body Style g. Exterior Color h. Interior Color i. Transmission - values include: Automatic or Manual j. Drivetrain values include: 2WD, 4WD, AWD, RWD 4. Each car may have many special Features and each Feature may be included on many cars. a. See the Homework 2 solution example for potential values. 5. Each car may have multiple warranties and each warranty may be used on many cars. Warranty values may include the number of years, the number of months, and/or the number of maximum miles covered. 6. Anderson's dealership is local to Omaha only, so location information for vehicles will not be tracked in this database 7. The Original cost of the vehicle paid by Anderson and the date the car was purchased must also be stored. 8. Additional features may be implemented based on the Homework 2 solution file posted to this assignment. Task 1 3: Sub-view/External Schema 3: Customer Appointments to View a Car and Purchase Orders: Task 1 - 3: Sub-view/External Schema 3: Customer Appointments to View a Car and Purchase Orders: If a customer is interested in purchasing a car, the customer will request an appointment to view the car, and a sales rep will contact them. See the screen shot below for the appointment request form. 1. The appointment request will include the specific car the customer is interested in viewing, the requested date and time of the appointment, the customer requesting the appointment, and whether the customer would like to discuss selling their car and/or financing options at their appointment. 2. If a customer chooses to purchase the car at their appointment, a purchase order will be created. The customer information, the car information, the date of the purchase, the date of delivery of the car, the sales-rep selling the car, and the purchase price of the car will be recorded and saved for a purchase order. 3. Finance information will NOT be stored for this assignment. Screenshot 1: Request Select a day and time for your test drive at CarMax Town Center in Town Center, GA Date Time Phone Number During your visit you can also (Optional) I Get an offer on your current car O Explore financing By clicking "Submit and providing your phone number, you agree that Car Max may call or text you (including via autodialer or prerecorded call) at the number you provide for marketing sales purposes, information relating to your appointments) or any other servicing or informational purpose. You do not need to consent to receive calls/texts to purchase from or sell to CarMax Msg & datarates may apply. Msg freq. varies. Text HELP for help. Text STOP to cancel. Texting Terms Privacy Policy SUBMIT Additional Business Rules: 1. Each customer may request many appointments, but each appointment is for a single customer. 2. Each customer may purchase many cars, but each purchase order is for a single customer. 3. Each salesperson may attend many appointments, but each appointment is assigned to a single salesperson. 4. Each appointment is for a single car, but each car can be included in many appointments. 5. Each car many appear in a single purchase order, and each purchase order is for a single car. Task 1 - 4: Sub-view/External Schema 4: Salesperson For each salesperson, the database must store the following information: 1. Name (first and last), Address (Street, Street 2, City, State, Zip code), e-mail, hire date, username, password (encrypted), cell phone number, office phone number 2. Note there is no screenshot for this requirement. Task 15: Sub-view/External Schema 5: Customer Appraisal Appointment Customers may sell their cars to Anderson's car dealership. If an appraisal appointment is requested by a customer, the customer must provide the year, make, model, and mileage of their car. See the screenshot below for the appraisal appointment request. If a customer chooses to sell their car to Anderson's, a sales order will be created. The customer information, the information about the car, the date of the sale, the date of delivery of the purchased car, the sales-rep approving the purchase, and the purchase price of the car will be recorded for the purchase order. Note that when a purchase of a customer's car is completed, the car becomes part of the inventory, and information about the car is saved with all other vehicles owned by Anderson's. See Sub-view/External Schema 1 above for data requirements for a car. Screenshot 1. Screenshot 1: We buy all kinds of cars Year Make Model Mileage SEE ABOUT YOURS Additional Business Rules: 1. Each appraisal appointment is for a single customer, and a single salesperson attends the appraisal meeting. 2. Both customers and salespersons may attend many appraisal meetings. 3. Each purchase order is for a single customer and a single vehicle. 4. Each customer may request many appraisal appointments and may sell many cars to Anderson's. 5. If the car is purchased by Anderson, the purchase price and date will be stored. TASK 2: Conceptual Schema (Logical Model) 25 Points After all Sub-view/External Schemas are created, return to the Logical Model screen and organize the entities and relationships until all items are visible. Ensure all data requirements are supported by your model and all relationships are defined AND LABELED with verb-phrases correctly. To receive full credit, the Logical Model and all Sub-views must use Information Engineering Notation. Data Modeler Hints: 1) To organize the entities on the Logical Model screen, right-click on the background of the Logical Model, select 'Layout' `Auto Layout' Choose a Layout (1, 2, 3, or 4) to have the software reorganize the layout for better viewing. 2) Right-click a relationship line and select 'add elbow' to allow the relationship line to 'bend'. 3) Right click on the background of the Logical model or any of the Sub-view/External Schema model screens and select 'Show' then 'Labels' to view your verb phrases/relationship names. 4) Reminder: If you created any super-type/sub-type entities, the primary key of a sub-type entity is the same as the primary key of the super-type, even though it is not visible in the Data Modeler model. Submitting this Assignment on Canvas: Create an Archive/Zip file inlcude the model file (*.dmd file), AND the associated directory/folder created by the Data Modeler software into a single file, then, post this zipped file to the Canvas Assignment link. Do NOT move the .dmd file inside the folder with the same name before archiving/zipping the files for submission
