DAT 560G: Database Design and SQL Fall 2022 Assignment #1

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DAT 560G: Database Design and SQL

Fall 2022

Assignment #1: E/R Diagrams


Instructions

1.   This is an individual assignment. You may not discuss your approach to solving these questions with anyone, other than the instructor or TA.

2.   Please include only your Student ID on the submission.

3.   The only allowed material is:

a.   Class notes

b.   Content posted on Canvas

c.   Textbook

4.   You are not permitted to use other online resources .

5.   Due online, before the next lab.

6.   There will be TA office hours. See the schedule on Canvas.

Assignment

Read sections 4.1 & 4.2 in the textbook

Submit answers to the following questions. When preparing a diagram, please include a brief explanation of your logic. Clarify any assumptions you make.  Handwritten solutions are fine.

Background

School Bus Inspection of Missouri (SBIM) is a nonprofit that collects information about yellow school bus inspections in the state. Most school districts in Missouri have a fleet of busses. These busses are used to bring students to and from school. Each bus needs to be inspected every year.

When busses in a school district are inspected, all busses are inspected at the same time. SBIM keeps track of the inspections of busses at school districts.

School bus inspections are carried out by local companies. When an inspecting company prepares the inspection of a school district, it sends the report to SBIM. There is also a fee associated with this report. This fee is paid by the school district.

Some  inspection  companies  only  service  one  school  district  in  Missouri.  Other  inspection companies service school busses in multiple cities. All inspecting companies inspect school busses made  by  the  most  popular  manufacturer,  REV.  Some  also  inspect  busses  made  by  other manufacturers.

There are quite a few different brands that manufacture school busses. Not all inspectors are certified to inspect all the brands.


 

Database

Develop the E/R diagram for a database for SBIM and the inspection data it receives. For now, we’ll focus only a few aspects of the database. Later parts of this assignment will expand the database. The database should incorporate information about the school districts, inspection companies, and inspections.

For each school district, SBIM maintains information about the main address, number of schools, number of school busses, and add 2 more attributes. The address is the key.

Information  about  inspecting  companies  includes: the  unique  business  name,  headquarters address (only the city name) and add 2 more attributes. All inspecting companies are certified to inspect busses made by REV. For the REV busses, SBIM maintains information about the date of first certification to inspect these busses, the REV representative that works with the inspection company, and the number of inspectors that are certified. For other brands, SBIM only stores information about the number of certified inspectors for that brand.

A school district may have been inspected several times in the past. The date of each inspection, and the fee paid, are saved in the database.

 

Question 1: [60 points]

Design the corresponding E/ER diagram. Remember throughout to include arrows representing cardinality, where relevant. Underline primary key attributes.

i.   Also, convert the diagram to relations. In other words, show the tables that would be used in the database. You do not need to include data, only the structure of the tables. Show primary keys underlined and foreign keys in italics.

ii.   Suggest 3 queries that you could ask of this database.

iii.   Suggest 1 query that requires additional attributes. Which attributes are needed to answer this additional query?

 

Question 2: [20 points]

Modify the database to include information for each bus in the school district. SBIM maintains only the bus ID (license number) for school busses. ALSO add the appropriate relation.

 

Question 3: [20 points]

Modify the database to include information about the school for each school district. Nearly all school districts have multiple schools . For each of these schools, SBIM keeps information about the type of school (elementary, high school, etc.), and the number of students. ALSO add the appropriate relation.

Now include cardinality for pairs of relations that you added.

 

 

 

 

 

 

 

 

 

 


2022-09-07