Database Assignment: Data Modeling, SQL Queries, and Normalization

This document presents a comprehensive solution to a database assignment, addressing key concepts in data management, data modeling, and SQL. The assignment is divided into several parts, including database management, data modeling, and SQL queries. The solution covers topics such as data value and cost, database vs. DBMS, and the impact of database changes. Data modeling includes entity-relationship diagrams, relationship instances, and data descriptions. SQL queries cover a range of operations, from basic queries to more complex joins, aggregations, and subqueries. The solution also addresses normalization, primary and foreign keys, and the differences between natural and inner joins. The assignment includes DDL statements, query outputs, and explanations of database concepts, providing a detailed understanding of database principles and practical SQL implementation.

Running head: DATABASE
DATABASE
Name of the Student
Name of the University
Author Note

1DATABASE
Table of Contents
Part 2: Database management..........................................................................................................3
Question 2a..................................................................................................................................3
Question 2b..................................................................................................................................3
Question 2c..................................................................................................................................3
Part 3: Data modelling.....................................................................................................................4
Question 3a..................................................................................................................................4
Question 3b..................................................................................................................................5
Question 3c..................................................................................................................................5
Question 3d..................................................................................................................................5
Question 4a..................................................................................................................................6
Question 4b..................................................................................................................................6
Part 4: SQL......................................................................................................................................7
Question 5a..................................................................................................................................7
Question 5b..................................................................................................................................8
Question 6a..................................................................................................................................9
Question 6b................................................................................................................................14
Question 6c................................................................................................................................15
Question 6d................................................................................................................................16
Question 6e................................................................................................................................17
Question 6f.................................................................................................................................17
Question 7a................................................................................................................................17
Question 7b................................................................................................................................18
Question 7c................................................................................................................................19

2DATABASE
Part 2: Database management
Question 2a
Data has cost and value both to an organization as the data defines the processes and events of
the organization which required costs and management to keep the gather and keep the data. On
other hand, the data has value, this is the reason behind the gathering and cost of the data. The
data are valuable for any organization in terms of analytics, insights and decision making for
their aims.
Question 2b
A database is just a collection of information that is kept and recorded in a way that it can be
easily retrieved and managed. It can be said that the database is a physical entity of all the
collections. On other hand, DBMS (Database Management System) is system which helps in
management of the data present in the Database. Without DBMS the operations on database and
its application would not be that much successful and efficient. The DBMS provide the
privileges to the programmers to create, delete, insert, update and manage the database in a
systematic approach. The whole system are the set of coded programs which helps in viewing,
defining and manipulating the data in databases. It can be said the DBMS establishes an
interaction between the user and Database.
Question 2c
i. On the conceptual and internal level of the database on changes will be required except
creating the table and populating it with the data. On application layer the different
customization are required on the basis of the given three scenarios. Any new application
process which require a new table and none of the existing table will not change

3DATABASE
anything. If new application a data which is used by another application is resolve by the
high capacity hardware. However, the relationship establishment may require between
the new and existing table. Lastly, for the process which require data which currently in
use, will require multiple views of the database according the application interface.
ii. Any schema changes will affect the database and the applications which uses the
database. It will affect the entire process of an application which is using the database as
backend. For example, changing a client table from full name to first name + last name
the schema will get change. In result application process will get affected or the interface
will result the first name as full name in the interface and next last name will get
displayed in the next results where it may not make any sense.
Part 3: Data modelling
Question 3a
List of Entities
Clients (ClientID, Name, CompanyName, Contact, Address, PhoneNumber, DateofFirstContact)
Event (EventID, Date, PilotID, ObserverID, Description)
Location (LocationID, Latitude, Longitude, Description, Note)
Relationships
Clients Books Event
Event Has Location

4DATABASE
Question 3b
i. There are total six relationship instances and three entity instances of each entity type are
present in the figure 2.
ii. Relationship description:
Generates:
o Each event can generate 0 or many data files.
o Each data file must be associated with at least one event.
iii. Entity Relationship Diagram
Question 3c
The above produced data description can help in working with the stakeholders to identify any
existing problem domains in the relations which can be corrected before integrating the relation
with the main schema. On other hand, the data description for the event details and data file can
help in defining client and drone details respectively while working with the stakeholders.
Question 3d
i. Each drone is commission once when the drone is purchased.
ii. Each maintenance session can have many drones commissioned.
iii. Each drone can be written off once in a maintenance session.
iv. Each maintenance session can write off multiple drones.

5DATABASE
v. Each drone can be repaired in many multiple sessions.
vi. Each multiple session can be associate with many drone.
No the multiplicity and optionality for the WrittenOffAt is not appropriate as one drone can be
written off at most one and at least zero. Also, one maintenance session can write off multiple
drones. For the RepairedDuring relation the maintenance session can repair 0 or multiple drones
and one drone can also have 1 or many maintenance sessions for repairing.
Question 4a
Primary keys and foreign keys are important in relational representations:
i. Primary key is attribute that uniquely identifies a tuple in a table. On other hand foreign key is
attribute which is the primary key in another table. It establishes a relationship between two
tables.
ii. Foreign keys are important for the relationship in a database to link the tables and the linking
should be logical as per the system requirements. The foreign keys are referenced to the primary
key of its parent. The referential integrity is important which maintains the integrity of the entire
database. As using these rules the incorrect insertion or update can be avoided.
iii. Declaring a primary key represents a constraint on the values possible in a relation to
guarantee a unique data for the primary key which will identify unique tuples.
Question 4b
i. Relation-for-relationship representation

6DATABASE
ii. Posted-key representation
Part 4: SQL
Question 5a
i. An update anomaly can arise when updating the employee details. As it can be seen Thaddeus
Jones has multiple events were he has piloted and updating his name from Thaddeus Jones to
Mr. Thaddeus Jones will result in updating all the tuples where the pilot is present. It is more
time taking and results in data redundancy.
ii. Event is in first Normal form as the criteria for the first normal form is full filled here.
According to first normal form each attribute must be singe valued, uniquely identified and same
domain.

7DATABASE
iii. According to the second normal form the relation must be second normal form and all the
non-key attributes should be fully functionally dependent on the key attribute of the relation.
Here the attributes depend on the EventID which identifies the tuples uniquely.
iv. According to the third normal form, a relationship should be in second normal form and there
should not be any transitive functional dependency. In this case, the PilotID depends on the
EventID and PilotName depends on the PilotID, hence there is transitive dependency between
EventID and Pilotname.
Question 5b
DDL statements:
Client Table:
CREATE TABLE client(
ClientID CIDdomain primary key,
Name NameString not null,
Contact TelephoneNumber not null
);
Event Table:
CREATE TABLE event(
EventID EIDdomain primary key
Date DATE not null,
PilotID EmployeeIDs not null,
ObserverID EmployeeIDs not null,
CHECK (PilotID <> ObserverID),
Client CIDdomain not null,
CONSTRAINT Booked FOREIGN KEY (Client) REFERENCES client);

8DATABASE
Question 6a
SQL
i. Query:
select pilot_id from event
union
select observer_id from event
union
select technician from maintenance
union
select signoff from maintenance;
Output:
pilot_id
----------
003451
002330
002347
002346
002341
025524
024232
(7 rows)
ii.Query:
select event_id, date, pilot_id, observer_id, manufacturer
from event
where client is null;
Output:

9DATABASE
event_id | date | pilot_id | observer_id | manufacturer
----------+------------+----------+-------------+--------------
E00286 | 2020-01-16 | 002341 | 002346 | FlyteSpeed
(1 row)
iii. Query:
select count(*)
from maintenance
where type = 'post-incident';
Output:
count
-------
4
(1 row)
iv. Query:
select count(t.*)
from (
select manufacturer
from drone
group by manufacturer
having count(serial)>0)
t;
Output:
count
-------
5
(1 row)
v. Query:
select start_date, manufacturer,serial

10DATABASE
from maintenance
where type = 'regular'
and notes like '%rotor%';
Output:
start_date | manufacturer | serial
------------+--------------+-------------
2019-08-20 | RotorDyne | RD142566243
2019-12-28 | Tyrell | 99544
2020-01-30 | FlyteSpeed | 1442522
(3 rows)
vi. Query:
select e.event_id, e.manufacturer, e.serial , d.type
from event e
inner join
drone d
on d.serial=e.serial;
Output:
event_id | manufacturer | serial | type
----------+--------------+-------------+-------
E00284 | RotorDyne | RD142566243 | RX12
E00283 | RotorDyne | RD142566243 | RX12
E00275 | RotorDyne | RD142562324 | RX12
E00282 | RotorDyne | RD142562324 | RX12
E00291 | FlyteSpeed | 1442522 | Evoc1
E00290 | FlyteSpeed | 1442522 | Evoc1
E00286 | FlyteSpeed | 1442522 | Evoc1
E00285 | FlyteSpeed | 1442522 | Evoc1
E00271 | FlyteSpeed | 1442522 | Evoc1

11DATABASE
(9 rows)
vii. Query:
select manufacturer, serial, start_date, type
from maintenance
where (technician='025524' or technician='002347')
order by start_date;
Output:
manufacturer | serial | start_date | type
--------------+-------------+------------+---------------
RotorDyne | RD142562324 | 2019-06-23 | commissioning
RotorDyne | RD142566243 | 2019-06-30 | commissioning
RotorDyne | RD142562324 | 2019-08-26 | regular
Tyrell | 99544 | 2019-08-29 | commissioning
RotorDyne | RD142562324 | 2019-10-10 | post-incident
Tyrell | 99544 | 2019-10-30 | regular
Tyrell | 99544 | 2019-12-28 | regular
FlyteSpeed | 1442522 | 2020-01-30 | regular
(8 rows)
viii. Query:
select distinct on(pilot_id) pilot_id,
count(event_id)
from event
group by pilot_id;
Output:
pilot_id | count
----------+-------
002330 | 5
002341 | 4