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*)

Remember, at the center of any academic work, lies clarity and evidence. Should you need further assistance, do look up to our Computer Science Assignment Help

Looking For Complete Assignment Sample Get It On WhatsApp