2023年11月25日

数据库代写 | Improving a database design

本次澳洲代写主要为数据库脚本相关project

Tasks
Step 1 (8 marks)
Improving a database design

This task requires access to either csora Oracle 19c database server, or one of data-
pc.. Oracle 19c database servers, or Oracle 19c running on a virtual machine.

Read the following description of a sample database domain.

A number of U7 football teams participate in a competition. Each team is identified by its
name. A unique location of a team consists of a city name and street name.

The players are described by a full name, date of birth and preferred playing positions in
a game. The players can have more than one preferred playing position. Each player
belongs to one team and each team consists of many players.

The teams play games in round-robin system, i.e. each team plays two games with all other
teams. One game is played at home and one game is played away. A moment when a game
starts and a venue of a game uniquely identifies each game.

The referees enforce the rules during the games. One referee per game is sufficient for the
games played by U7 teams. Due to a large number of teams and games each referee must
rule in more than one game. We do not have a lot of referees and we can assume that a
pair of attributes first name and last name uniquely identifies each referee. A referee is
also described by a name of a city he/she comes from.

A database designer created ad hoc the relational schemas without passing through a stage
of conceptual modelling. It is why we do not have a conceptual schema.
The designer created SQL scripts dbcreate.sql and dbdrop.sql. The scripts can
be used to create and to drop the relational tables designed by the designer. The scripts are
available from Moodle. The files dbcreate.lst and dbdrop.lst contain the reports
from processing of the scripts.
An amateur approach applied by the designer contributed to several mistakes in the
relational schemas. In the first step, your task is to remove the mistakes made by a
database designer.

To do so, repeat the following procedure for each relational schema listed in a script
dbcreate.sql.

(1) Use a short description of a database domain, your knowledge of sport competitions
and the contents of SQL script dbcreate.sql to discover nontrivial functional
dependencies valid in a relational schema.

(2) Use the functional dependencies found in the previous step to derive the minimal keys

valid in a relational schema.

(3) Find the highest normal form valid in a relational schema.

(4) If a relational schema is not in BCNF then decompose it into the smallest number of
relational schemas in BCNF.