Exam 1 ERD
docx
keyboard_arrow_up
School
University of Houston, Downtown *
*We aren’t endorsed by this school
Course
3306
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
5
Uploaded by jnicholson4
MIS3306 Database Management Systems
Exam 1: Part II
Required for Grading:
I am Jordan Nicholson (type your full name) and I complete this assignment following the
UHD academic integrity policy.
Instructions (READ):
1.
Use Microsoft Visio
or Draw.io
to draw the diagram. Diagrams made by any other tools will not
be graded.
2.
Use IE (crow’s foot) notation
. Diagrams with any other notations will not
be graded.
3.
The diagram should specify: a.
entities, b.
keys, c.
attributes (divide an attribute further into simple attributes when appropriate), d.
relationship types (identifying or non-identifying), and e.
cardinalities (MAX and min on each side). 4.
Use a specialization hierarchy when appropriate. Grading is based on the completeness of the diagram.
5.
Add a textbox with your full name inside the diagram (no point without your name). The figure below is an example.
6.
Paste your diagram in this document and submit this document
. Any submission other than this document will not
be graded.
1
1.
(8 points) The following are two tables with data for a Customer-Order database. Answer the following questions based on these two tables.
a.
Explain entity integrity. Do the tables exhibit entity integrity?
Answer: Both tables have entity integrity because both primary keys have unique values and are not null.
b.
Explain referential integrity. Do the tables exhibit referential integrity? Answer: The tables do not have referential integrity because the values in the foreign key (CustomerID) in the Order table do not match the primary key in the Customer table. 2.
(7 points) Dormitory Administration System You are designing a dormitory administration system. The university has several dorms, each has many rooms. A dorm is identified by its dorm ID. Besides, a dorm has a dorm name, street address, city, state, and zipcode. Each room is identified by a combination of dorm ID and room number. Besides, a room has number of beds, and monthly rent. Answer:
2
Dorm
PK
DormID
Dorm_name
streed_address
city
State
zip_code
Room
PK
Room_number
FK
DormID
number_beds
monthly_rent
Made by Jordan Nicholson
3.
(20 points) Law Firm System (Must Use Supertype-Subtype Entities)
You are asked to design a database system for a law firm. The system requirements are summarized as the follows.
There are three types of persons in the system: staff, attorney, and clients. Any data about a person in the system must be stored in one
of these three categories (and cannot be simply stored as “person”). In addition, a person cannot be two categories at the same time.
o
The system has data about the staff’ ID, first name, last name, phone, and address. o
The system should contain data about attorneys, including their ID, first name, last name, phone, address, certificate, and specialty. o
The system has to track information about clients. A client, identified by its ID, has information including first name, last name, phone, and address.
3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
An attorney may have several appointments in the system. As well, a client may make several appointments in the system. Data about an appointment includes appointment ID, time, date, reason, and the client and the attorney for the appointment. One appointment may have one or
more case note. Each case note has its own note ID, description, and recommendation.
Answer:
Staff
PK
Staff_ID
First_name
Last_name
Phone
Attorney
PK
Attorney_ID
First_name
Last_name
Phone
Clients
PK
Client_ID
First_name
Last_name
Phone
Address
Address
Certificate
Specialty
Address
Appointments
PK
Appointment_ID
Time
Date
Reason
Client_ID
FK
Attorney_ID
FK
Made by Jordan Nicholson
4.
(15 points) Book publisher system. Draw an ER diagram for the following case. The E-R diagram should include entities, attributes, identifiers, and relationships. In the diagram, place a textbox 4
at the top-left corner of your diagram and type Made by FirstName LastName
in the textbox (5 points).
A book is identified by its ISBN number, and it has a title, a price, and a date of publication. A book is published by a publisher, which has its own publisher ID number and a name. Each book has only one publisher, but one publisher typically publishes multiple books over time.
A book is written by one or multiple authors. Each author is identified by an author number and has a name and date of birth. Each author has either one or multiple books. A book may have several authors.
Additional information is needed regarding the relationship between a book and its authors. An author may have different royalty rates for different books that he/she wrote. Also keep in mind
that a book may have several authors (hence several royalty rates).
Answer:
Book
PK
ISBN
Title
Price
Date_publication
Publisher_ID
FK
Publisher
PK
Publisher_ID
Name
Author
PK
Author_number
Name
Date_Birth
5