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

Get It Done! Today

Applicable Time Zone is AEST [Sydney, NSW] (GMT+11)
Not Specific >5000
  • 1,212,718Orders

  • 4.9/5Rating

  • 5,063Experts

Highlights

  • 21 Step Quality Check
  • 2000+ Ph.D Experts
  • Live Expert Sessions
  • Dedicated App
  • Earn while you Learn with us
  • Confidentiality Agreement
  • Money Back Guarantee
  • Customer Feedback

Just Pay for your Assignment

  • Turnitin Report

    $10.00
  • Proofreading and Editing

    $9.00Per Page
  • Consultation with Expert

    $35.00Per Hour
  • Live Session 1-on-1

    $40.00Per 30 min.
  • Quality Check

    $25.00
  • Total

    Free
  • Let's Start

Get
500 Words Free
on your assignment today

Browse across 1 Million Assignment Samples for Free

Explore MASS
Order Now

My Assignment Services- Whatsapp Tap to ChatGet instant assignment help

refresh