De Montfort University IMAT5103: Stage 2 Logical Database Design

This document presents a comprehensive solution for a database design assignment, focusing on logical database design and Oracle SQL implementation. The assignment covers the creation of normalized tables, including appropriate primary and foreign key relationships, data types, and integrity constraints. It details the population of tables with suitable data, demonstrating the use of SQL statements for inserting values and creating indexes to optimize data retrieval. Furthermore, the solution includes the development of six required SQL queries, providing justifications for each query choice and demonstrating the ability to retrieve specific data elements. The solution received an overall grade of 96.7%, reflecting a high level of accuracy and understanding of database design principles. This solution is perfect for students looking for help with their database assignments. The assignment was for the module Database Systems and Design (IMAT5103) at De Montfort University.

Stage 2 Marksheet (50%) – Logical Database Design & Oracle SQL
implementation/querying
Criteria Justification
Tables – quantity
correct and in
appropriate
format, with
primary/foreign
keys indicated and
appropriate
100%
The tables that has been created, justifies the requirement such as
Staff Table had StaffId as its primary key, Rooms table has
RoomID as its primary key and in Booking table these two primary
key are playing role of Foreign keys.
Tables – Well
normalised tables,
with all
appropriate non-
key attributes.
100%
The normalization of the tables can be done such as Staff and Client
can be normalized.
Creation of Oracle
tables with
sufficient and
suitable integrity
constraints
(including data
types and lengths)
implemented
95%
If one Room ID is associated to one Client Id then Same Room Id
cannot be linked with any other Client because of 1 to 1
relationship. Hence integrity constraints will interrupt if it’s done
so.

Population of
suitable and
sufficient data into
the Oracle tables
using appropriate
SQL statements
90%
The SQL statements are appropriate as the Tables has been created
properly with accurate attributes. The statements for inserting
values are also accurate. The observed data are accurate such as
values of Staff table.
Creation of the
four most
appropriate
indexes with
written
justifications as to
why the indexes
are important to
implement
100%
Indexes has been created on Client type, Bride, Groom of wedding
client, Room name and Room type of Room table. Indexing of the
table speed up the process of data retrieval methods, the required
data can be identified easily.
Development of the
six required
syntactically
correct queries;
that together cover
the required SQL
SELECT
statement elements
as defined, with
reason(s) for each
95%
The queries that has been chosen to be implemented is helpful to
retrieve required data such as what payment is to be done for
particular event as in Query2.

query choice.
OVERALL
GRADE
96.7%