Database Assignment Solution - ER Diagram, Relational Schema, 3NF

This assignment solution addresses database design concepts. It begins with an ER diagram and relational schema, followed by an analysis of 3NF normalization. The solution identifies partial dependencies and proposes a refined schema. The assignment then explores binary and ternary relationships, explaining the appropriate use of each and advocating for a single ternary relationship in the context of a 'borrows' scenario. The solution provides a detailed explanation of database concepts and the relationships between entities in a database. This assignment solution is perfect for students seeking to understand database design principles.

DB_ASSIGNMENT SOLUTION
STUDENT NAME:
10 November, 2016

Contents
ANSWER 1: ER DIAGRAM................................................................................................................2
ANSWER 2: RELATIONAL SCHEMA...............................................................................................2
ANSWER 3.2........................................................................................................................................3
ANSWER 4...........................................................................................................................................4

ANSWER 1: ER DIAGRAM
ANSWER 2: RELATIONAL SCHEMA

1. Director (dID, fullName)
2. Movie (mID, title, length, dID*)
3. Actor (aID, fullName)
4. Appears (mID*, aID*)
5. Cinema (cID, name, address)
6. Theatre (number, cID*, type, capacity)
ANSWER 3.2
3.2.1 The functional dependencies are:
a. staffID -> staffMobileNumber, staffName
b. storeID -> storeAddress
c. staffID -> storeID
d. salaryGroup, hoursWorked, dateOfShift -> amount
e. salaryGroup -> baseRate
3.2.3: the relation is not in 3NF or BCNF because there are partial dependencies like staffID
-> staffMobileNumber, staffName or storeID -> storeAddress. The attributes are not
completely dependent on a primary key. Hence the relation cannot be functionally dependent
on key attribute and is not in 3NF.
3.2.4: the final schema is:
Store (StoreID, storeAddress)
Staff (staffID, staffMobileNumber, staffName, storeID*, salarygroup*)
Work (staffID*, dateOfShift, hoursWorked, amount)
Salarygroup_info (salarygroup, baseRate)

ANSWER 4
We use binary relationships when two entities participate in a relationship. And ternary
relationships are used three entities participate in a relationship. There are cases which use
separate binary relationships rather than ternary relationships when different fields are used
by each of them. Whereas a common ternary relationship is used for a relationship which will
use common attributes between entities.
We will use ‘one ternary relationship’ diagram to explain the relationship well
because the relationship is common that is, ‘borrows’. Hence there is no need to use two
different relationships with same attributes. This will only increase the complexity of the
database in the end. This way in the relationship ‘borrows’, we can record call_no of the book
and studentID or staffID who so ever borrows the book along with ‘dateof loan’.