IS-4420 Database Fundamentals
homework | assignment | ios代写 | lab代做 | database – 这是一个database面向对象设计的practice, 考察database的理解, 是比较典型的ios/database等代写方向, 这个项目是lab代写的代写题目
lab 3
- Enhanced Entity-Relationship Diagram (EERD)
- database Normalization
20 points
Setup
1 - Log into LucidChart and create a blank document.
2 - Search for the Entity-Relationship (ERD) library, pin it, and remove all other libraries.
Note
- You are encouraged to collaborate on homework assignments with your peers.
- If you get stuck, attend tutoring office hours.
- Send questions via email to the Me & the TAs.
- Assignments will take time, start them early.
- Class attendance will be rewarded by getting hints in class about the assignments.
1: Building Enhanced ERDs (4 points)
Build an Enhanced ERD using the below business description.
Pacific Hospital keeps information on patients and hospital rooms. The system assigns each patient a PatientID, Patient First & Last Name, BirthDate, and PatientType. Some patients are resident patients (they spend 1 or more nights in the hospital), and others are outpatients (they are treated and released the same day).
Outpatients are given a set of instructions to take home after their visit, which is tracked in the database.
Resident patients are assigned to a room. Each room is identified by a room number. The system also stores the RoomType (private or semiprivate), and RoomFee. Over time, each room will have many patients that stay in it, and a patient may stay in many rooms. Every room must have had a patient, and every resident patient must have a room. When a resident patient stays in a room, the hospital needs to track the check in & check out dates.
2: Building Enhanced ERDs (4 points)
Build an Enhanced ERD using the below business description.
Granite Sales Company keeps information on employees and the departments they work in. For each department, the DepartmentID, Name, MailboxNumber, and PhoneExtension is tracked. A department can have many assigned employees, and each employee is assigned to only one department. Employees can be salaried employees, hourly employees, or contract employees. All employees are assigned an EmployeeID. This is kept along with the employees FirstName, LastName and Address (StreetAddress, City, State, & Zip). For hourly employees, HourlyWage and WeeklyHours are stored (e.g, the company may target 40 hours / week for some, 32 hours / week for others). Some salaried employees are salespeople that can earn a commission in addition to the base salary. For all salaried employees, the AnnualSalary amount is recorded in the system. For salespeople, their commission percentage on sales (SalesCommission) and commission percentage on profits (ProfitCommission) are stored in the system. For example, John is a salesperson with a base salary of $50,000 per year plus 2% commission on the sales price for all sales he makes plus another 5% of the profit on each of those sales. For contract employees, the BillingRate, BeginDate, and EndDate of their contract are stored.
3: Building Enhanced ERDs (4 points)
Build an Enhanced ERD using the below business description.
The FlyRight Aircraft Maintenance (FRAM) division of the FlyRight Company (FRC) performs all maintenance for FRCs aircraft. Produce a data model that reflects the following business rules.
- FlyRight employs mechanic and non-mechanic employees (not all employees are mechanics).
- For employees regardless of type, FRC tracks FirstName, LastName, Salary, and EmployeeType.
- For mechanic employees, FlyRight tracks the Title and MechanicType.
- Mechanic employees specialize in: o Engine Mechanics (EN). These mechanics must have a license. Therefore, their LicenseNumber is tracked. o Airframe Mechanics (AF). These mechanics are categorized into levels (junior, mid-level, senior). Therefore, their LevelOfExpertise is tracked. o Avionics Mechanics (AV). These mechanics are categorized into avionics specialty (communications, navigation, display). Therefore, their AvionicsSpecialty is tracked.
- Training courses are offered to the mechanics. TrainingID and a Description are tracked for each training that is conducted.
- FRC tracks all training courses taken by each mechanic, and collects the Date, Certification (Y/N), and Performance. Over time, mechanics are expected to take many training courses, and each training course is expected to be taken by many employees. Some mechanics may not have taken any training courses for a short time after they are hired, and some training courses are added to the FlyRight system before any mechanics have taken the course.
- FRC keeps a history of the employee salaries regardless of employee type. To track employee salary history, the date and salary of the employee is tracked when employee pay changes.
4: Database Normalization (4 points)
Normalization recap:
First Normal Form (1NF):
Each relation has a primary key, all attributes are atomic (no repeating groups),
and all attributes are Functionally Dependent on the primary key.
Second Normal Form (2NF):
Each relation is in 1NF, and there are no Partial Functional Dependencies.
Third Normal Form (3NF):
Each relation is in 2NF, and there are no Transitive Functional Dependencies.
Use the following relation & functional dependencies to answer the questions.
Functional Dependencies
StudentID FirstName, LastName, StateCode
StateCode StateName
CourseID CourseName
StudentID, CourseID, Semester StudentGrade
1 - Describe the data anomalies you would be exposed to if you were to store your data in the table
above.
Insertion Anomaly: It occurs when inserting data and some values are missing, in this case you
will have to insert null.
Update Anomany: It is crucial to get the right row to update or risk updating several rows. Risk
of data inconsistency.
Deletion Anomaly: It occurs when a deletion operation affects untargeted rows. This can lead
to loss of data.
2 - Is the table in First Normal Form (1NF)? If so, what is the Primary Key?
Yes it is in 1NF. It has a composite PK which are StudentID and CourseID.
3 - Convert the model to Second Normal Form (2NF).
4 - Convert the model to Third Normal Form (3NF).
5: Review (4 points)
Answer the following questions:
1 - Describe how to implement the relationship between Supertypes & Subtypes.
Supertypes typically have members, properties. These properties are typically other types (classes in oop). Example, we have type Car, and Motorbike as subtypes named transportation_type.
Now we create a supertype, Transportation thas has one of its member, property as transport_type to reference either Car or Motobike.
2 - Describe how to implement the Subtype Discriminator when you have overlapping subtypes.
Disjoint Subtypes : In the example above, where we have a supertype Transportation, When inserting an instance of Transportation, when we specify C as the value for transportation_type, the instance shall be assigned its appropriate subtype.
Overlapping Subtypes : In an event where an instance of a supertype must create two or more instance os subtypes. In an example, say a mechanical part, tire, can be manufactured, sold, or manufactured and sold. Below is a table of how to capture these scenar ios using booleans.
Part IsManufatured IsPurchased
Manufatured Yes No
Purchased No Yes
Manufactured & Purchased Yes Yes
3 - Describe the difference between Total Participation (Specialization) vs Partial Participation
(Generalization).
In the image above, let us assume that an Employer can or cannot have a license. Lets assume
we have 3 employers, and two have a license while one does not have a license.
Total Participation: Occurs where we cannot have a license and not have an employer. In our
case, there is a total participation between license and employer.
Partial Participation: Occurs where we can have an employer and not have a license. In our
case, there is a partial participation between employer and license.
4 - Describe the costs & benefits of Database Normalization.
**1. Stored data becomes consistent.
- Stored data has reduced redundancy.
- By reducing redundancy we reduce cost of computing resources needed to use the** data.
Submission
Save this file as a PDF with the naming convention Lab_3_FirstName_LastName.pdf and upload it to the assignment page in Canvas.