Databasse

Only available on StudyMode
  • Download(s) : 216
  • Published : May 3, 2013
Open Document
Text Preview
FACULTY OF MEDIA, INFORMATION & COMMUNICATION TECHNOLOGY

DATABASE SYSTEMS 201 Due Date: Chapters Covered: 06 MAY 2013 Database System Data Analysis Entity Relationship Modeling Enhanced E-R Models Normalization Structured Query Language Joins and Views Sub queries and Schema 100 DIP –DBS 201

Maximum Marks: Unique Assignment Number:

Background This assignment is based on the content to be covered in the first semester. Naturally all material provided or prescribed will be used. Purpose: The purpose of the assignment is to: a. Ensure that you come to thorough grip with content, in particular of the provided or prescribed material and b. Gain an in depth understanding of the core concepts and the significance of the theories discussed in the module.

1

PC TRAINING & BUSINESS COLLEGE HIGHER EDUCATION AND TRAINING FACULTY OF MEDIA, INFORMATION AND COMMUNICATION TECHNOLOGY DIPLOMA IN INFORMATION TECHNOLOGY ASSIGNMENT COVER SHEET: DATABASE SYSTEMS 201

Name of Learner…………………………………………………….… Student No: ………………………….. Module…………………………………………………………………..…Date: …………………………………….. ICAS Number……………………………………………………………..Year: …………………………………….. ASSESSMENT CRITERIA

Question Number 1 2 3 4 Total Examiner’s Comments

Mark Allocation 30 20 30 20 100

Examiner’s Mark

Moderator’s Marks

Moderator’s Comments

NB: All Assignments will be handled in as it forms part of continuous assessment that goes towards the final mark.

2

QUESTION ONE 1.1

[30 MARKS]

Using the INVOICE table shown below, write the relational schema, draw its dependency diagram, and identify all dependencies, including all partial and transitive dependencies. Assume that the table does not contain repeating groups and that an invoice number references more than one product. (10) (Hint: This table uses a composite primary key)

Attribute Name INV_NUM PROD_NUM SALE_DATE PROD-LABEL VEND_CODE VEND_NAME QUANT_SOLD PROD_PRICE 1.2

Sample Value 211347 AA-E3422QW 15-JAN-2012 Rotary sander 211 NeverFail,Inc. 1 R49.95

Sample Value 211347 QD-300932X 15-JAN-2012 0.25-in drill bit 211 NeverFail,Inc. 8 R3.45

Sample Value 211347 RU-995748G 15-JAN-2012 Band saw 309 BeGood, Inc 1 R39.99

Sample Value 211348 AA-E3422QW 15-JAN-2012 Rotary sander 211 NeverFail,Inc. 2 R49.95

Sample Value 211349 GH-778345P 16-JAN-2012 Power drill 157 ToughGo, Inc 1 R87.75

Using the answer from 1.1, remove all partial dependencies, write the relational schema, and draw the new dependency diagram. Identify the normal forms for each table structure you created. (10) Using the answer from 1.2, remove all transitive dependencies, write the relational schema, and draw the new dependency diagram. Identify the normal forms for each table structure you created. (10)

1.3

QUESTION TWO

[20 MARKS]

The FlyRight Aircraft Maintenance (FRAM) division of the FlyRight Company (FRC) performs all maintenance for FRC’s aircraft. 2.1 Produce a data model segment that reflects the following business rules:  All mechanics are FRC employees. Not all employees are mechanics.  Some mechanics are specialized in engine (EN) maintenance. Some mechanics are specialized in airframe (AF) maintenance. Some mechanics are specialized in avionics (AV) maintenance. (Avionics are the electronic components of an aircraft that are used in communication and navigation.) All mechanics take periodic refresher courses to stay current in their areas of expertise. FRC tracks all course taken by each mechanic – date, course type, certification (Y/N) and performance.  FRC keeps a history of the employment of all mechanics. The history includes the date hired, date promoted, date terminated, and so on. 2.2 Given those requirements create the Crow’s Foot ERD segment.

3

QUESTION THREE

[30 MARKS]

The following relations keep track of airline flight information: (Each Query carries 2marks)     Flights (flno: integer, from: string, to: string, distance: integer, departs: time, arrives:...
tracking img