Task 1 (ERD): Draw a conceptual data model (ERD) using either crow’s feet to mode the data requirements for the chosen database. Your ciagram should include all entities, relationships (including names) and attributes, primary key (underlined), cardinality and participation (optional / mandatory)

KING’S OWN INSTITUTE*
Success in Higher Education
Assessment 4
Assessment Type: Database project – Group assessment
Assessment Purpose: This assessment will help students to design and develop a database based on a given scenario specification. This assessment contributes to learning outcomes b,c,d,e, and f. The students will identify the entities and their relationships and write SQL queries to implement the database. Students are expected to improve the database performance, including the structure, database relationship, integrity, and other aspects to make the database more secure, efficient, and presentable to business stakeholders.
Value: 40%
Due Date: Week 11
Topic: The group will identify a project scenario and get it approved by the tutor before proceeding further.
Group collaboration rules:
Students will work in small groups of 3-4 students in a group. Each group is supposed to have at least two group meetings. The meeting minutes should be documented for each meeting and attached as an Appendix of the group work report, dearly indicating who was present, any issues, and actions, and agreed timelines. The group work must indicate that a fair and reasonable distribution of work among the individual croup members has been achieved. Each group member is expected to make a reasonable contribution to this group assignment. “Free riders” are net allowed. If a group member is found not to contribute to this assignment, a penalty (or even a zero mark) will oe applied to that member.
Task Details: Complete the following tasks and document your work in your written assessment. There are two components to this task-Groupwo’k implementation and individual self-reflection report. For group work, students will be required to analyse and determine the requirements for the client’s business needs and complete the database design and development tasks satisfying the user requirements. Thegroup is required to answer the following questions in their report.
Task 1 (ERD):
Draw a conceptual data model (ERD) using either crow’s feet to mode the data requirements for the chosen database. Your ciagram should include all entities, relationships (including names) and attributes, primary key (underlined), cardinality and participation (optional / mandatory) symbols; and assumptions yoj have made, e.g. how you arrived at the cardinality / participation for those not mentioned or clear in the business description, etc. Your ER Diagram must include Seven to Ten etities. Your attribute names and primary and foreign keys should be indicated as per the conventions given in the lecture slides (i.e. attributes as proper nouns, primary key underlined, and foreign keys in italics. All many-to-many relationships should be resolved
Task 2 (Relational data structures)
Create relational data structures that translates your conceptual data model into a logical data model which includes relation names, attribute names, primary keys, and foreign keys.
Task 3(Normalisation): For each relation in task 2, identify a list of functional dependencies and the level of normalization achieved. Provide justification anc a step-by-step process fo’ each level of normalization.
Task 4 (DDL and DML Scripts): Translate the relational data structures into a set of base relations and create the database Using Microsoft SQL Server. Follow the guidelines below for this task:
a. Create a new database and follow a standard naming convention for tables and attributes.
b. Create relationships between tables and enforce the necessary entity and referential integrity, and suitable constra nts.
c. Insert records into each of your tables (there should be a minimum of 5 records per table).
d. Export the DDL and DML script for your database and copy it into your report.
KING’S OWN INSTITUTE
Success in Higher Education
Task 5 (SQL queries): Write any 5 sample Queries using SQL Server. These queries should use the concept of Order By, LIKE, aggregate functons, BETWEEN, Joining. These SQL queries must be tested, and the SQL statements, together with their results screenshots should be placed in the report.
Task 6 (Group Skills):
Write a self-reflection report (no more than 250 words) describing your expe’ience of designing and developing the database in you’ group. Please be advised that the groups on Moodle record the collaboration between group members. This group information will be used to validate your reflection report.
Each student must write and submit their ‘eflection report individually. The report may include but is not limited to the fo lowing:
. Discuss your positive experience of working in the group such as collaboration and cooperation among members. You may also mention any challenges/difficulties that you have faced in accomplishing the group project such as lack of commitment.
i. Comment on any limitations and/or strengths of your design
iii. Comment on whether your database meets all the system requirements as specified
iv. Mention any assumptions made.
v. Reflect on how ethics and privacy considerations influenced your design and development process
Submission requirements details: Submit in Moodle by Sunday of Week 10,11.59 pm. Group work report (Task 1-5) is one component that needs to be submitted by the group leader only. The other component is the individual contribution self-reflection (Task 6), which needs to be submitted by each student individually. Students should submit a report that follows the format of a business/professional report and contains, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content: