IMAT5103 Database Systems and Design - EER Modelling & SQL

Verified

Added on  2023/04/21

|12
|558
|120
Homework Assignment
AI Summary
This document presents a comprehensive solution for a database systems and design assignment, focusing on EER modeling and SQL implementation. The assignment begins with selecting a scenario, in this case, a football event system, and then proceeds to develop a conceptual database design, including business rules. Following this, a logical database design is created, detailing tables for matches, teams, stadiums, and referees. The solution then demonstrates the creation of these tables using Oracle DBMS and includes the implementation of four useful indexes for each table. Data population is performed, followed by the writing of six SQL queries to retrieve specific information from the database. The assignment concludes with a bibliography. This assignment is designed to assess the student's ability to analyze a problem domain, design a database system, and implement it using appropriate modeling techniques and data manipulation languages, as required for the IMAT5103 Database Systems and Design module.
Document Page
Running head: DATABASE SYSTSEM AND DESIGN
Database systems and design
Name of the Student:
Name of the University:
Author Note
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
1
DATABASE SYSTEM AND DESIGN
Stage 1: Scenario and Conceptual Database Design Task
1.1: Selection of the case upon which the database design and implementation is to be
based
The football event system has been selected here for the description of the database
and its implementation.
Document Page
2
DATABASE SYSTEM AND DESIGN
Task 1.2: Provide a conceptual database design for your scenario & the list of enterprise
rules being modelled
Business Rules
The main business of the football event database are:
The details of the teams are to be stored in the database.
The database should be storing the details of the referees in the system.
Document Page
3
DATABASE SYSTEM AND DESIGN
The database should be able to store the details of the stadiums, which would be
hosting the matches.
The database would able to store the details of the matches that
Stage 2: Logical Database Design and Oracle SQL Implementation/querying
Task 2.1: Provide a Logical Database Design for your scenario
Matches (MatchID (pk), HomeTeam (fk), OpposingTeam (fk), StadiumID (fk), MatchDate,
RefereeID (fk))
Teams (TeamID (pk), TeamName)
Stadium (StadiumID (pk), StadiumName)
Referee (RefereeID (pk), RefereeName)
Task 2.2 Create the tables using Oracle DBMS
Database tables
Teams
Stadiums
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
4
DATABASE SYSTEM AND DESIGN
Referee
Matches
Document Page
5
DATABASE SYSTEM AND DESIGN
Task 2.3: Create the four most useful indexes on your tables
Teams
Stadiums
Referee
Matches
Document Page
6
DATABASE SYSTEM AND DESIGN
Task 2.4: Data Population
Teams
Stadium
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
7
DATABASE SYSTEM AND DESIGN
Referee
Matches
Task 2.5: SQL Query writing
Query 1
Select P2502395TEAMS.TEAMNAME, P2502395REFEREE.REFEREENAME
From P2502395TEAMS
Document Page
8
DATABASE SYSTEM AND DESIGN
Inner Join P2502395MATCHES On P2502395MATCHES.HOMETEAM =
P2502395TEAMS.TEAMID
inner Join P2502395REFEREE On P2502395REFEREE.REFEREEID =
P2502395MATCHES.REFEREEID;
Query 2
Select P2502395TEAMS.TEAMNAME
From P2502395TEAMS
Where P2502395TEAMS.TEAMNAME LIKE 'M%';
Query 3
Select P2502395REFEREE.REFEREENAME
From P2502395REFEREE
Where P2502395REFEREE.REFEREENAME LIKE 'M%';
Document Page
9
DATABASE SYSTEM AND DESIGN
Query 4
Select P2502395STADIUMS.STADIUMNAME
From P2502395STADIUMS
Where P2502395STADIUMS.STADIUMNAME LIKE 'E%';
Query 5
Select COUNT(P2502395MATCHES.MATCHID)
From P2502395MATCHES;
Query 6
Select COUNT(P2502395STADIUMS.STADIUMNAME)
From P2502395STADIUMS
Where P2502395STADIUMS.STADIUMNAME LIKE 'E%';
tabler-icon-diamond-filled.svg

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
10
DATABASE SYSTEM AND DESIGN
Document Page
11
DATABASE SYSTEM AND DESIGN
Bibliography
Lee, S., Tewolde, G. and Kwon, J., 2014, March. Design and implementation of vehicle
tracking system using GPS/GSM/GPRS technology and smartphone application. In Internet
of Things (WF-IoT), 2014 IEEE World Forum on (pp. 353-358). IEEE.
Pfaff, B., Pettit, J., Koponen, T., Jackson, E., Zhou, A., Rajahalme, J., Gross, J., Wang, A.,
Stringer, J., Shelar, P. and Amidon, K., 2015. The design and implementation of open
vswitch. In 12th {USENIX} Symposium on Networked Systems Design and Implementation
({NSDI} 15) (pp. 117-130).
chevron_up_icon
1 out of 12
circle_padding
hide_on_mobile
zoom_out_icon
[object Object]