ICT201 Design Of Relational Database

ICT201 Design Of Relational Database :

Assessment 2

Unit code/name:	ICT201 Database Systems
Type of Assessment:	Database Design-Group Assignment
Unit     Learning      Outcomes addressed:	ULOs 2 and 3
Weighting | marks:	40% | 40 marks
Submission Date:	To be submitted in week 10
Assessment Task:	The Design, Building, And Querying Of A Relational Database.

Marking Guide Rubric

Marking Criteria	Lecturer Expectation		Marks
Justifying       and      identifying dependencies of the table	(a)   Assumptions about dependencies among the table’s columns are made, and justifications are based on the table’s sample data and on knowledge about service business.		2
Relational         schema         & Dependency diagram in 1NF	(a) The relational schemas and dependency diagrams are correct. The dependency diagram has proper labels for all dependencies.		3
Dependency diagrams in 3NF	(b) Dependency diagrams and relational schemas are correct in 3 NF. Dependency diagrams contain no multi-valued attributes, and naming conventions are met.		5
ER-D diagram	(c) ER-D is created correctly using the Crow’s Foot style, correctly specified entities, attributes, relationships, and multiplicity. Minimum and maximum cardinality are specified and shown on the diagram.		4
CREATE database	(d) SQL statements are correct. The databases were created successfully with primary keys, foreign keys, and other attributes mentioned for all tables. (Screenshot: SQL query and output)		6
INSERT into database	(e)  All SQL statements are correct. All rows of data are inserted correctly into each of the tables. (Screenshot: Query and output)		3
SQL statements	(f.1) To list the member number, member name, zip code and dinner date for all club members (Screenshot: Query and output)		2
	(f.2) To determine how many times the ‘Stuffed Crab’ have been ordered (Screenshot: Query and output)		2
	(f.3) To list the names of the members from the city ‘Murkywater’ and had the dinner date as ‘15-Mar-2018’. (Screenshot: Query and output)		2
	(f.4) To list the total bill (new column) for each member (3 Screenshots: Query and output)		6
	– New column (TOTAL_BILL)	1
	New attributes (Query and output)Insert values (Query and output)	3
	– Total bill calculation – Recursive JOIN on Table 1 (Query and output)	2
Final report	MS Word document with the screenshot of queries and output (around 8 screenshots). The report should mention individual contributions/efforts.		5
Total			40

Assessment Description

This assessment is a Group Assignment, and you must work in groups of 3-5 students.

Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, plan the meals, keep track of who attends the dinners, and so on.

• Each dinner serves many members, and each member may attend many dinners.
• A member receives many invitations, which are mailed to many members.
• A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn, or a fish entree, a baked potato, and string beans.

Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table 1 below.

Tasks to be completed:

1. Given the table structure illustrated in Table 1, write the relational schema and draw its dependency diagram. Label   all   transitive   and/or   partial   dependencies. (Hint: This structure uses a composite primary key.)

Table 1: DINNER_INVITE Sample values

• Break up the dependency diagram you drew in Task (a) to produce dependency diagrams        in        3NF,         and         write         the         relational         schema. (Hint: – You might have to create a few new attributes. Also, make sure that the new

dependency diagrams contain attributes that meet proper design criteria; that is, make sure there are no multivalued attributes, that the naming conventions are met, and so on.

–    Add new attributes for dinner, entrée and dessert cost)

• Using the results of Task (b), draw the Crow’s Foot ERD.
• Create databases using MySQL, with primary keys, foreign keys, and other attributes mentioned for each entity of the ERD in Task (c) using proper constraints.
• Populate the tables created in task d with some significant data (See Table 1 for sample data).
• Create SQL statements to satisfy the following:
  • Write an SQL statement to list the member number, member name, zip code and dinner date for all club members.
  • Write a SQL statement to determine how many times the ‘Stuffed Crab’ have

been ordered?

• Write an SQL statement to list the name of the members who are from the city

‘Murkywater’ and had the dinner date as ‘15-Mar-2018’.

• Write an SQL statement to list the total bill (new column) for each member (member name). The price for each dish code is as given in Table 2 below: (Hints: – Add new attributes for dinner, entrée and dessert price, and insert

respective values

• total bill = dinner + entrée + dessert using WHERE clause
  • Recursive JOIN Table 1 to calculate total bill and display member number, member name and invite number)

Table 2: CODE_VALUES Sample values

ATTRIBUTE CODE	SAMPLE VALUE
D15	$35
D12	$33.50
EN3	$14.50
EN5	$12.50
DE8	$16
DE5	$11
DE2	$13.50

Assignment Submission

This is a group activity, and students must work with their respective group members to fulfil the assessment. No individual submission will be accepted. You must nominate one person in the group to coordinate the assignment submission. The assignment must be submitted online in Moodle by ONLY ONE member of each group.

You will not receive any marks for the assignment if your group members collectively report against you for non-participation or non-cooperation.

You need to submit two items:

1. The report in MS Word with following details:
   1. First page should be title page (Title= “Assessment 2”), group member names and ID, and the contribution (your contribution part of assignment)
   1. ERD, the normalisation and the dependency diagram in MS Word document format,
   1. Queries (execute your queries and take the screenshot) and output screenshots (Around 8 Query + output screenshots).
2. Database file (Using export option in My SQL workbench or Mysqldump command),

Put both the files (MS Word, SQL file) in a folder named ‘assm2_groupid_groupclass’

(e.g. assm2_group1_TUE_morn), zip it and submit it on Moodle.

Note: Failing to follow the submission guidelines may affect marks.

=== End of Assessment ===

Visit:https://auspali.info/

Also visit:https://www.notesnepal.com/archives/767

For solution:

WhatsApp

+610482078788

+61482073403

+61482072848

[email protected]