硕士课程assignment：Database Management

Faculty of Science & Information Technology科学与信息技术学院   School of Design, Communication and Information Technology学校的设计，通信和信息技术 The University of Newcastle 纽卡斯尔大学   INFO6001 – Database Management 1数据库管理 Assignment 2: DCIT Access Project DCIT Access项目   Due: Wed, August 10, 2011 at 1 pm WORTH 15% of final assessment mark. In this assignment, steps in the logical and physical database design stage are conducted. In this Assignment, you need to complete the following steps:   1. Map the EER model to the relational model.地图能效比模型关系模型   You can use either: • The EER Model submitted for your Assignment 1. Revise it based on feedback provided by your tutor, or EER模型提交修改你的导师所提供的反馈意见的基础上，或另外，完成下面给出部分EER模型，以满足系统的要求。 • Alternatively, complete the partial EER model given below to meet the systems requirements.    Once you have completed the EER model along with its data dictionary; convert the EER model to a relational schema. Document the relational schema in DBDL (Sample DBDL format is given in the below):一旦你已经完成的能效比模型以及数据字典; EER模型转换成关系模式。文档关系模式在DBDL （在下面）样品DBDL格式：   ISBN (id, number, itemNo) Primary Key id Alternate Key number Foreign Key itemNo references Book(itemNo)  ON UPDATE CASCADE, ON DELETE CASCADE   Also DBDL format is shown in Figure 17.8 page 515 in your main text.   2. Next, normalize the schema to Boyce-Codd Normal Form (if any relation is not already in BCNF)   3. Write a SQL script which creates the DCIT Resource Access database (i.e. creates the tables and their constraints).   4. Write appropriate CHECK constraints to maintain data integrity mentioned in the requirements document. You should implement the following business rule using a DML trigger:    Maximum items loaned at any one time  A member cannot borrow more than the maximum number of items specified in his/her privileges at any given time.   5. Create a project report for submission. The project report should include:  a. Requirement Specification  b. EER Diagram and Data Dictionary c. Normalized Relational Schema in DBDL. Ensure that normalisation steps are shown if any. d. SQL script which creates the DCIT Resource Access database (i.e. creates the tables and their constraints). e. SQL statements satisfying the transaction requirements and business rules   In all the steps above, you must clearly state any assumptions and/or design decisions considered.   Submission    This is an INDIVIDUAL assignment!   In this assignment, you must submit the project report which includes:  • Requirement Specification  • EER Diagram and Data Dictionary • Normalized Relational Schema in DBDL • SQL script which creates the database (i.e. creates the tables and their constraints). • SQL statements satisfying the transaction requirements and business rules   Method of submission:  Both softcopy submission and hardcopy submission are required: • It must be submitted as a word document via Blackboard -> Assignments -> Assignment2 (will be checked by TurnItIn). • Print the document (make sure its content is the same as that of the file   that goes through Turnitin successfully), hand in the hardcopy to the lecturer by the due time. The hard copy must have on the front a signed copy of the cover sheet which is available from: http://www.newcastle.edu.au/study/forms/index.html You have to attend your formally registered lab of week 12 to get your assignment 2 checked and marked.   Percentage of total grade: This assignment carries 15% of the overall grade.