Database Management Systems Assignment 1 - MIS310, Spring 2020

This assignment solution addresses key concepts in database management systems, including identifying themes, anomalies (insert, update, delete), primary keys, and functional dependencies within relational databases. The solution analyzes a sample data set for Garden Glory, identifying themes and anomalies within the data. It then explores the design of a medical practice database, determining candidate keys, primary keys, and foreign keys. The solution also discusses entity and referential integrity rules and normal forms (1NF, 2NF, and 3NF). Finally, the document examines functional dependencies and primary key identification in a university database scenario. The solution demonstrates a strong understanding of database design principles and their application in various contexts.

Running head: DATABASE MANAGEMENT SYSTEM
Database Management System
Name of the Student
Name of the University
Author’s name:

1DATABASE MANAGEMENT SYSTEM
Table of Contents
Part 1................................................................................................................................................2
Part 2................................................................................................................................................2
Part 3................................................................................................................................................3
Part 4:...............................................................................................................................................3
Bibliography:...................................................................................................................................5

2DATABASE MANAGEMENT SYSTEM
Part 1
1.a) There are two themes in this table. One is Lawn Mow and other one is Garden
Service. Lawn mow is most popular service. This service is used for every property available.
Garden service is comparatively less popular service. This service has been used in only Jeferson
Hill office property.
1.b) First anomaly is that for each service provided to any property, the property details
need to be added. This can lead to insert anomaly as if any information of property is wrong then
database would lose integrity. The second anomaly is that if any property details is updated then
all the instances need to be updated. This can lead to update anomaly.
1.c) PropertyName and Description cannot be a primary key. This is because Easstlake
Building has used Lawn mow service twice. This will prevent the database to maintain rules of
primary key constraint.
1.d) The determinant of amount is both property name and description. This is because,
for each property one service cost is always same. Taken as an example, Eastlake Building has
used lawn mow service and the cost is 42.50 for each instance.
Part 2
2.a) DrivLic# and P_SSN are the candidate keys of PATIENT table.
2.b) The primary key for PATIENT table is P_SSN. This is because P_SSN has been
used in the visit table. As per the database convention, primary of one table can only be reference
key of other table.

3DATABASE MANAGEMENT SYSTEM
2.c) Primary key of visit is composite primary key. The composite primary key contains
D_SSN, P_SSN and DateofVisit. This is because DS012 and PD001 has two instances in visit
table. However, the DateofVisit is different for both instances.
2.d) D_SSN refers to D_SSN of doctors table. P_SSN refers to PATIENT table’s P_SSN.
2.e) Visit does not maintains Entity Integrity Rules. Some columns has null values.
2.f) It does not maintain Referential Integrity rule because P_SSN of last instance is null
which cannot be null as it is part of composite primary key.
Part 3
3.a) The primary key is VID.
3.b) The table is not in second normal form because it contains partial dependency.
3.c) Vehicle(VID, VMiles, VMake, VModel) – Owner(OwnerID, OwnerLastName,
OwnerFirstName) – Service(VID, ServiceDate, ServiceDescription, ServiceCharge)
All the new tables has been created based on the partial dependencies.
3.d) The database is in third normal form.
3.e) Vehicle(VID, VMiles, VMake, VModel) – Owner(OwnerID, OwnerLastName,
OwnerFirstName) – Service(VID, ServiceDate, ServiceDescription, ServiceCharge)
Part 4:
4.a) TA‐G  TA‐name
STU‐G  STU‐name, STU‐major

4DATABASE MANAGEMENT SYSTEM
TA‐G, STU‐G  Date, Starttime, Duration and ChairOfMajor
4.b) As per the functional dependencies, the primary key is composite primary of (TA‐G,
STU‐G)

5DATABASE MANAGEMENT SYSTEM
Bibliography:
Arulraj, Joy. The Design and Implementation of a Non-Volatile Memory Database Management
System. Diss. Microsoft Research, 2017.
Zhang, Bohan, et al. "A demonstration of the ottertune automatic database management system
tuning service." Proceedings of the VLDB Endowment 11.12 (2018): 1910-1913.