Reference no: EM132260167

Database Systems Assignment - Database Design and Implementation

Assignment Group formation: Each Project Group may consist of 1 student to 5 students, i.e., S<6, where S is the number of students in a Project Group. Elect a group leader if your Project Group has multiple students. Members of a Project Group can be from the same tutorial session or different tutorial sessions taught by the same lecturer (A/P Wang Lipo teaches tutorial sessions MON 0930-1100, 1900-2030, 2030-2200, TUE 1330-1500, WED 1430-1600, THUR 1400-1530, whereas A/P Chen Lihui teaches tutorial sessions THUR 1130-1300, FRI 1430-1600). Each student can be in at most 1 Project Group. Please do not request to have more than 5 students in a Project Group. You do not need to use Blackboard to form groups - you just need to state who are in the Project Group on page 1 of your assignment report.

Database Design:

1. Propose a database case study or scenario. If your group has S students, your database should have at least (S + 3) tables. For example, a group with 2 students will need to have at least 5 tables. Write down the requirements on the database, i.e., database scenarios (see example scenarios in Tutorials. Avoid database jargons, such as entities and sub-types, in your scenario). Do not use exactly the same scenarios given in the lectures and tutorials of this course, i.e., Acorn Valley Furniture and Mountain View Community Hospital. It is alright to use other examples appeared in the lectures and tutorials, e.g., employees are assigned to projects, although you are encouraged to minimize the similarity between your database scenario with those covered in lectures and tutorials. More precisely, it would be unacceptable to have more than half of the entities/relationships in your database design the same as the entities/relationships in any 1 slide of the lecture notes or in any 1 tutorial question. While it is "acceptable" to have less than half of the entities/relationships in your database design the same as the entities/relationships in any 1 slide of the lecture notes or in any 1 tutorial question, between two assignments of the same quality otherwise, the less similar one is preferred. Please make a decision on what scenario to use based on the above and how you design your database, without asking the lecturer to evaluate your scenario/design, since this is an assessment.

2. Draw the ER diagram for your scenario.

3. Convert the ER diagram to a set of table structures (relations). If any tables are not in 3NF, normalize them to 3NF.

Database Implementation:

4. The requirements below are for Microsoft Access users. If you use database software other than Microsoft Access, do something equivalent.

5. Create the tables in Microsoft Access in Design View. Implement data integrity (default value, range values, etc). Add 5-10 fictitious records in each table.

6. In your Access file, generate at least (S + 1) Data Entry Forms (using Form Wizard, for entering data into tables), (S + 1) Queries (using Query Design View), and (S + 1) Access Reports. Each Access report should include individual information, itemized information, and aggregate information (for example, the patient information, various treatments, and total expense, as in Question 3d of Tutorial 1).

7. To speed up data retrieval, would it make sense to create indexes? If so, create 2 indexes and show they work.

Overall Assignment Report:

8. Write an assignment report using Microsoft Word on your assignment in the following order (there are no requirements on the maximum or minimum number of pages):

1. Title page: In a table, list all member names in your Project Group (the group leader first, followed by other group members in an alphabetical order, if there are multiple students in your group). All names must be identical to those in matriculation cards (e.g., if your name is written on your matriculation card as "Chou Jay", do not write it as "Jay Chou". Do not leave out or add any spaces in your name either. For example, if your name is written on your matriculation card as "Vijay Kumar", do not change it to "Vijaykumar"). Simple details like these would help tremendously in finding your name in the marking list. Each group member may be tasked with different parts of the project but will be responsible to all parts of the project.

2. Requirements on your database (i.e., scenario for your database).

3. ER diagram (You must use Microsoft Word drawing tools to draw your ER diagram - see example ER diagrams in tutorials and lecture notes. Do not include an image of your ER diagram drawn with other drawing tools, since the lecturers would not be able to verify whether you just scanned or downloaded the image somewhere. You may add Screen captures of relationships in Microsoft Access for the entire database; however, this does not replace the required Microsoft Word drawing of ER diagram).

4. Table structures (3NF), or also known as relational model, including referential integrity.

5. Screen captures of table designs (table design view), Screen captures of tables with data.

6. Screen captures of queries, including query design views and query results.

7. Screen captures of forms for data entry (design view and data entry view).

8. Screen captures of Access reports (design view and report results).

9. Screen captures of any indexes created.