Due date: 1 August 2012 NB: Make sure you select the correct unique number for your module from those indicated below. Unique number 759546 Tutorial matter covered in the prescribed book Chapters 1 - 3: Chapter 5: Chapter 6: Chapter 7: Chapter 12: Chapter 14: Chapter 16: Revise as for Assignment 01 Entity relationship modelling Advanced data modelling Normalisation of database tables Transaction management and concurrency control Distributed database management systems Database connectivity and web development
What is a composite entity, and when is it used?
[4 marks] [8 marks]
Briefly, but precisely, explain the difference between single-valued attributes and simple attributes. Give an example of each.
Use the following business rules to create an ERD using UML notation. Write all appropriate multiplicities in the ERD. • A department employs many employees, but each employee is employed by one department. • Some employees, known as “rovers,” are not assigned to any department. • A division operates many departments, but each department is operated by one division. • An employee may be assigned many projects, and a project may have many employees assigned to it. • A project must have at least one employee assigned to it. • One of the employees manages each department, and each department is managed by only one employee. • One of the employees runs each division, and each division is run by only one employee.
The FlyRight Aircraft Maintenance (FRAM) division of the FlyRight Company (FRC) performs all maintenance for FRC’s aircraft. Produce a data model segment that reflects the following business rules: • All mechanics are FRC employees. Not all employees are mechanics. • Some mechanics specialise in engine (EN) maintenance. Some mechanics specialise in airframe (AF) maintenance. Some mechanics specialise in avionics (AV) maintenance. (Avionics are the electronic components of an aircraft that are used in communication and 14
navigation.) All mechanics take periodic refresher courses to keep abreast of developments in their areas of expertise. FRC tracks all courses taken by each mechanic, that is, the 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. (Note: The “and so on” component is, of course, not a real-world requirement. Instead, it has been used here to limit the number of attributes you will show in your design.)
Given those requirements, create the ERD segment using UML notation.
Suppose that you have been given the table structure and data shown in Table 1, which was imported from an Excel spreadsheet. The data reflect that a professor can have multiple advisees, can serve on multiple committees and can edit more than one journal. Table 1: Sample records Attribute name Sample value EMP_NUM LECT_RANK EMP_NAME DEPT_CODE DEPT_NAME PROF_OFFICE ADVISEE 123 Professor Ghee CIS Computer Info. Systems KDD-567 1215,2312, 3233, 2218, 2098 PROMO, TRAF APPL, DEV JMIS, QED, JMGT
104 Asst. Lecturer Rankin CHEM Chemistry BLF-119 3102, 2782, 3311, 2008, 2876, 2222, 3745, 1783, 2378 DEV
118 Assoc. Lecturer Ortega CIS Computer Info. Systems KDD-562 2134,2789, 3456, 2002,2046, 2018, 2764 SPR, TRAF JCIS, JMGT
101 Assoc. Lecturer Smith ENG English PRT-345 2873,2765, 2238, 2901, 2308 PROMO, SPR DEV
On the basis of the information in Table 1, do the following: 5.1 5.2 5.3 Draw the dependency diagram. Identify the multivalued dependencies. Create the dependency diagrams to yield a set of table structures in 3NF.
Suppose you are asked to evaluate a DBMS in terms of lock granularity and the different locking levels....