ISYS1055 The Relational Model Assignment Sample

Database Concepts

Question 1. The Relational Model

The entity types and their associated attributes are,

• Branch: branch-No (unique), address (composite: street-number, city, postcode).
• Employee: employee-ID (unique), employee-name.
• Customer: customer-ID (unique), customer-name.
• Account: account-number (unique), balance.
  • Give all likely FDs. Do not include trivial or redundant FDs.

Customer-ID à Customer-name, Account-number,

Branch-No à address, employee-ID, customer-ID

Employee-ID àemployee-name, branch-No

Account-number àbalance, branch-No, customer-ID

• Give {customer-ID}+ based on the FDs

Customer-ID àbalance, branch-No

Customer-ID àaddress, employee-ID

• Give relations to keep data for the entity types and their relationships as described for the ABC bank. Specify the primary key (underline) and any foreign key.

Branch(branch-No, street-number, city, postcode).

Employee(employee-ID, employee-name, branch-No*)

Customer(customer-ID, customer-name, account-number*, branch-No*)

Account(account-number, balance, branch-No* , customer-ID*)

Question 2. Normalisation

Consider the following relation schema about project meetings:

PMG(projID, title, type, manager, jobID, start-date, end-date, contractor, contractNo)

FDs based on business rules are:

• projID à title, type, manager
• manager à type
• jobID à projID, start-date, end-date, contractor
• projID, title, jobID à contractNo
• contractNo à jobID, contractor, start-date, end-date
• jobID à contractNo

2.1. The given FDs have redundancies. Give the minimal basis for the given FDs.

• projID à title, type, manager
• manager à type
• jobID à projID, start-date, end-date, contractorNo

2.2.

• Non-prime attributes are not functionally dependent on the super key.
• No transitive functional dependency of non-prime attributes on any super key is not allowed.
• It virtually doesn’t eliminate all the redundancies.
• The relation doesn’t ensure referential integrity.

2.3.

Project(projID, title, type, manager-ID*)

Manager(Manager-ID , manager-name, project-ID*)

Job(jobID, start-date, end-date, Contract-No*,project-ID*)

Contract(contractNo, contractor, job-ID*)

Question 3. ER to Relational Schema Mapping

Staff(empNo , givename, surname)

openSession(sessionNo , opendate, opentime)

property(propertyNo , streetnumber, street, suburb, postcode, sessionNo*)

owner(ownerNo, address, phoneNo, ABN, entityname, ownerType, propertyNo*)

client(CustNo, givename, surname, phoneno, crediting)

Lease(leaseNo, startdate, enddate, custNo*, PropertyNo*)

You Might Also Like :

computer science it assignment sample

com00150m department of computer science research proposal

Looking For Complete Assignment Sample Get It On WhatsApp