AP/ITEC 3220 – Using and Designing Database Systems Winter 2023 Section Q Assignment # 1

Objectives

The purpose of this activity is to practice Database Design using ERDs, Relational Data Model and SQL.

Submission Requirements

•    The submission of this assignment will be through Crowdmark where you will upload the solution question by question. You will receive the link to access Crowdmark assignment in your email and on eClass.

•    Only one student per group is required to submit.

•    Submit solution for each question in its designated place in the form of PDF. In each question write the name of all team members. Solutions uploaded wrongly will not be graded.

•    Please add the statement given in Appendix A in response to Question 7. Both team member must submit this statement.

•    Deadline  is  Mon.  February  27,  2023,  before  16:59.  Late  submission  is  not  permitted  under  any circumstances. Till then you can modify your submission.

•    Your submissions will be verified using Turnitin (or some other suitable tool) for originality. 60% or more similarity will be awarded zero in the assignment and reported to the department. We may report similarity less than 60% if it is of significant nature.

Note: In database design, variations are always possible. If the business requirements are taken care of in a proper logical way as per the data modeling guidelines, the answer will be considered correct.

Warning: Please add the necessary headings and labels to your submission so that the TAs can understand the different parts properly. Anything that we cannot understand will be awarded zero.

General Instructions

•    Make sure that your submission is easy to understand; clearly add the question number, part number, captions and foot notes wherever required. If the TA can’t locate the answer, then you will get zero.

•    Make sure that images/screenshots are clear. If the image is big then split it into multiple parts. Clearly write their purpose. You can add multiple images even if the question statement doesn’t say so to make sure that your answer is easy to comprehend.

•    Highlight the significant parts of each image so that TA can easily identify the required answer.

•    Add necessary explanation to make sure that TA can understand different parts of your submission.

ER Modeling

Question 1:                                                                                                        [10 Marks]

Assume your company is developing a generic digital camera online purchasing system that can be sold to different camera stores. An initial analysis phase of the project has resulted in the following informal description of relevant data for the system.

A store will be selling a variety of digital cameras and lenses. The digital cameras can have following mutually exclusive optional features:

1.   An ability to replace lenses.

2.   Cameras with an electronic viewfinder.

3.   Cameras with an optical viewfinder.

4.   Cameras with an optical rangefinder.

Relevant properties of all cameras are the model number, retail price and the number currently in stock. Properties of cameras without an ability to replace lenses that are relevant include a focal length range. Cameras with an ability to replace lenses are related to at least two or more lenses.

Properties of a lens that are relevant include the model number, retail price and the number currently in stock.

Online customers are either domestic customers or foreign customers. Properties of customers that are relevant include a unique customer number, a customer name, an email address and a shipping address.

Each customer may have any number of purchase orders. Each purchase order is for only one unit of any product at a time, either a camera or a lens, and will also have a selling price.

Draw an ER/EER schema diagram for this application and upload it to your submission on Crowdmark. Discuss any assumptions you make, and then justify your ER/EER design choices. You must not add any additional attributes or data requirements.

Question 2:                                                                                                        [10 Marks]

Consider a database application which provides a web-based platform where students can practice on tasks assigned by the instructors. In terms of design, following are some major requirements:

•   Instructors will first create their accounts. Relevant details for the instructors are: email, affiliation and name.

•   Instructors will create Courses and add Students to each course. Relevant details for Courses are: course code, term and year. Relevant details for Students are: email and student ID.

•   In Courses, Instructors will create one or more assignment and an assignment will consist of one or more tasks. Each task has a grade.

•   Students  will  work  on  Tasks  and  Assignments  and  will  be  awarded  grade  based  on  their submission.

Design  an  ER/EER  schema  diagram  for  this  application  and  upload  it  to  your  submission  on Crowdmark. Discuss any assumptions you make, and then justify your EER design choices. You must not add any additional attributes or data requirements.

Mapping from ER to Relational Model

Questions 3-4:                                                                                         [10 x 2 Marks]

Convert the ERDs in questions 1-2 to logical relational schema. Draw the schema using the approach used in Figure 5.7 in the book i.e., relations, attributes, keys, foreign keys must be properly indicated. Upload these schemas to your submission on Crowdmark.

Questions 5-6:                                                                                         [10 x 2 Marks]

Write the SQL code to create relational schemas from questions 3-4 in your MySQL database. To indicate the success of these steps you need to perform following actions for each of the question:

•   Submit the code/script for each schema creation separately to your submission on Crowdmark.

•   Run these scripts on your MySQL server, one by one, using the run script file option.

•   Show the list of tables (there should be none) before the above scripts are executed using “SHOW TABLES” and upload the screenshot to your submission on Crowdmark. You need to do this step only once and then reuse the image for both the questions, 5-6.

•   Show the list of tables after the two schema creation scripts are executed using “SHOW TABLES” and upload the screenshot to your submission on Crowdmark. You may do this step only once and then reuse the image for both the questions, 5-6.

•   DESCRIBE statement can be used to see the schema of a table as shown in the example below:

o DESCRIBE all the tables from the two questions, one by one, and upload the screenshots to your submission on Crowdmark. Do this separately for each question and upload the screenshots to the appropriate question space.

NOTE: Since these two questions involve several images/screenshots, make sure your submission is properly organized and comprehensible.