IntroductiontotheRelationalDatabase Major ICT702

ICT702IntroductiontotheRelationalDatabase Major Assignment T1-25

MajorAssignment(IndividualAssignment)Database Design and Implementation

PurposeandLearningOutcomesPurpose

Thepurposeoftheassignment isto provide studentswith theopportunityto apply knowledge

and skills developed during the semester with reference to:

1. Interpretation of business rules from a case
2. Conceptual data modelling through the creation of an Entity Relationship (ER)
3. Application of DDL and DML components of SQL to:

1. creates and populates a relational database; and
   1. query the created relational

TimelinesandExpectation

Thepercentagevalue ofthis Assignment is40%of thecoursemark.

Due:theduedateisonSunday04/05/202523:59week9,Refertothecourseoutline. Minimum Time Expectation: 35 hours.

Students are required to complete the assignment individually.Students are expected to submit the required report and details (see below) to the submission box in their Moodle shell.

AssignmentRequirements

Overview

Studentsareexpectedtodevelopandprovidetheirowncasestudyandtheninterpretthatto create an ER Model of the system.

They are then expected to provide a physical implementation of the ER model in the form of the DDL to create the required tables, attributes, and relationships.Students are then required to provide the DML to insert sufficient information into the database to answer a set of queries.

They should also provide proof of the running DDL and DML queries by providing images of the output obtained.It is a requirement of this assignment that students use Xampp for the database components. The submission must be presented in the format of a professional report. Further information is given in the Detailed Requirements and Marking Criteria sections of this document.

Case Study Requirements

1. Scenario choice: your chosen scenario must include at least Ten to Tweleve entities that are related to each other. This can be anything you choose, but you are advised to choose something you know, to make the exercise easier. An example could be a hobby you have perhaps you enjoy a sport, and you wish to develop a database that models some form of performance related to the teams and games played in this sport. Another example could be a part-time job, and you may wish to develop a database to assist some aspect of that business. Another example could be some area of science, perhaps an engineering-type application that models a power station or a factory or an experiment of some kind. Because this brief is very open-ended, you are recommended to check your chosen scenario with the teaching staff to make sure it is suitable. A final consideration when thinking of your scenario is the list of SQL queries that you will need to execute within your database to prove the creation of tables and insertion of data. For instance, you will need to include certain search and statistical functions, so your scenario will need to be sophisticated enough to make these queries possible. PLEASE NOTE: each student is required to develop a UNIQUE scenario it will not be permitted for two students to use the same scenario.
2. Your ER Diagram must includeTen to Twelve entities. There is no upper bound on how many entities you choose, but you are advised to include no more thanFifteen. Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality, and participation indicated. You will need to normalise all your entities, to resolve any many-to-many relationships.

Observe the following restrictions when creating your scenario:

• Include a specialization hierarchy, with super types and overlapping or disjoint sub-
• Include an example of both composite and surrogate primary
• One(ormore) ofyour entities must haveanumeric
• One(ormore)ofyour entitiesmust contain analphanumeric(varchar)
• One(ormore) ofyour entitiesmust containaDate
• 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, and you may wish to include a discussion of normalisation (Functional dependency Diagrams), also including the normal form that each entity is in and why that is optimal.
• For each entity, you must create some example data for that entity. Include at least 5 rows of data for each entity.
• Data Dictionary needs to be provided before creating the tables and inserting the
• Write the SQL statements to create the table structures from your developed ERD. The structures should contain the attributes specified in your ERD. Use data types that are appropriate for the data that will need to be stored in eachEnforce primary key and foreign key constraints as indicated by your ERD. Write the SQL code to insert your data into the SQL table structures.

DetailedRequirements

• This assignment is an individual assignment. It is a requirement of this assignment that students use XAMPP or Microsoft SQL for the database components.
• Students should submit a report that follows the format of a business/professional report and contain, at a minimum, a Title Page, Table of Contents, Executive Summary and References (if cited) and the following content:

1. An ER model of the case study system. This should conform to the third normal form. Students should be aware there are disjoint subtype entities. Students can use any drawing package to present the ER diagram, but the diagram should use the Crows foot notation and conform to the standards identified in (Coronel and Morris 2018). These include that entities are shown in a rectangle with the name of the entity in grey at the top separated from two columns below with PK, and FK identifiers, where appropriatein the first column and attributes in the second column. Primary key attributes are to be separated from other attributes by a line across the rectangle. All entities are to be in upper case and attribute names are to be in capitalise. All relationships should be labelled and identified as mandatory or optional. All connectivity, participation and cardinalities (if there are specific limits) should be shown.
2. A screenshot of the XAMPP showing the creation of a database with the name
3. The DDL statements are required to implement the conceptual data model above. Appropriate constraints must be created. Students must follow the same naming conventions i.e. lower for keywords, uppercase names for tables and attributes with an underscore between words and a new line for each clause. Students should use the default schema i.e. there is no need to create one.
4. DMLstatementstoinsertsufficient(5rows)dataintothedatabasetocorrectly display the inserted data in tables.

Submission Requirements

• StudentsarerequiredtosubmitaWordorPDFreportthatincludes thefollowing:

-Casestudy

-ExecutiveSummaryandIntroduction

-Assumption

-ERDDiagram

-RelationSchema

-BusinessRules

-CardinalitiesandConnectivity

-Normalisationsteps (3NF)thatincludedependencydiagramsforeachentity.

-DataDictionary

-DDLincludesscreenshotsfrom Xamppthatshowthedatabasename(studentID)

-DMLincludesscreenshotsfrom Xamppthatshowthedatabasename(studentID)

-ATxtfilethat includesalltheSQL code(DDLandDMLCommands).