Databasse
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
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