CS 4320/Homework 5
Question 1 [80 Points] Draw an entity-relationship diagram depicting the scenario outlined next. Make sure to represent all constraints using the representation seen in class.
We are creating an entity-relationship diagram of the database of a computer shop. The shop sells computer models that are characterized by a (unique) model name, an amount of disk space, an amount of main memory, and the CPU speed. Also, each model is associated with a price. The shop has multiple branches. Each branch is associated with a (unique) name and an address, consisting of street name and number, as well as the zip code. Physical computers are identified by an ID and associated with exactly one model and with exactly one of the branches at which they are stored. Vendors are associated with a name and are identified by thIDr social security number. For specific days of the week, they are assigned to specific branches.
Name Street
Copyright By PowCoder代写 加微信 powcoder
Number ZIP
Stored- At
Question 2 [40 Points] Translate the entity-relationship diagram, created for the last question, into corresponding SQL DDL statements, using the method seen in class.
Create table Weekday(name text primary key);
Create table Branch(name text primary key, street text, number
int, zip int);
Create table WeekDay(name text primary key);
Create table Vendor(SSN int primary key, name text);
Create table Assigned(BName text, Wday text, VName, primary key
(BName, Wday, VName), forIDgn key BName references branch(name),
forIDgn key Wday references WeekDay(name), forIDgn key Name
references Vendor(SSN));
Create table Model(name text primary key, cpu text, ram int,
disk int);
Create table Computer(ID text primary key, model text, branch
text, forIDgn key model references Model(name), forIDgn key
branch references Branch(name));
Question 3 [40 Points] Consider a relation storing information on employees that has the following columns (with short names between parentheses):
– Employee ID (ID)
– Employee name (EN)
– The department ID (DI)
– The department name (DN)
– The job type (JT)
– The monthly salary (MS)
– The assigned building (AB)
The following dependencies are known:
– Employee ID is the primary key
– The department ID implies the department name (DI -> DN)
– The department name implies the building (DN -> AB)
– The department, together with the job type, implies the salary (DI, JT -> MS)
Bring the relation above into BCNF via the method seen in class. Justify each decomposition step with one to two sentences. Use the column short names to describe relations.
The original relation R(ID,EN,DI,DN,JT,MS,AB) is not in BCNF since it contains DI and DN, the FD DI -> DN holds, and DI is not a key for the relation. We decompose into
– R1(ID, EN, DI, JT, MS, AB) and R2(DI, DN)
The resulting relation R1 is not in BCNF since it contains columns DI, JT, and MS while FD DI, JT -> MS holds (DN, HT is not a key). Hence, we decompose R1 into
– R3(ID, EN, DI, JT, AB), R4(DI, JT, MS)
From the given FDs, we can infer DI -> AB via transitivity (exploiting DI -> DN and DN -> AB). Hence, R3 is still not in BCNF (as DI -> AB, DI is not a key nor is the FD trivial, and DI and AB appear as columns in the same relation). Hence, we decompose R3 into
– R5(ID, EN, DI, JT), R6(DI, AB)
The resulting database is in BCNF.
Submit answers to all questions as a single .pdf document on CMS (e.g., you may use a drawing tool to create the entity-relationship diagram, then include it into a text document and export that as .pdf). This homework can be submitted by groups of up to three students.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com