TM254 TMA 02: Database Design, SQL Queries, and Data Modeling

This assignment solution addresses various aspects of database management and SQL, covering topics such as database management systems (DBMS), the three-schema architecture, and data modeling. It includes an analysis of entity types, relationship types, and the creation of entity-relationship diagrams. The solution also explores normalization, primary and foreign keys, and their importance in maintaining data consistency. Furthermore, it delves into SQL, providing examples of SQL statements, query analysis, and the conversion of relational representations to SQL DDL. The assignment also discusses database security, user privileges, and the significance of locks for concurrent access, addressing potential issues like update anomalies. The solution covers a wide range of database concepts, providing a comprehensive understanding of database design, implementation, and management.

COVER PAGE

Question 3
(a) Why database management system (DBMS) is much more than just a database.
DBMS is much more than just a database because a DMBS is a software that helps database
users to create and manage databases by providing a systematic way to create, update, retrieve
and manage the data making up the database. Thus the database itself is the collection of tables
made of columns and rows which are related to one another while the DBMS is the software
designed to act as an interface between the database and the users of the database. By using a
DBMS to manage a database, the data in the database can be easily accessible and consistently
managed. A DBMS manages the data in the database, the engine in which the database runs on
and the database schema.
(b) The three-schema architecture has 3 levels;
 Physical level- in this level physical storage of the data making up a database is stored in
physical addresses on storage devices.
 Conceptual level- The conceptual level is used by database designers and describes the
structure of the database. Data models like Entity relationship diagrams are used in this
level.
 External level (user view) - The external level shows the relation of the data with the end
users. In this level different segments of the database are defined based on the groups
of users.
Based on the three-schema architecture described above, the following changes will be done on
each level to accommodate these new requirements.
(i) A new application process requires a new table in the database, and uses none of the
existing tables.
 Physical level- No change is required in the physical level because data physical
storage of the data is handled by the database management system (DBMS).
 Conceptual level- At this level, the data model should be updated to
accommodate the new tables added.
 External level- Changes in the user views are required to group the users who
will be using the new table.
(ii) A new application process is required that uses data already in use by an existing
application process. However, the performance requirements for the new application
require the database to use new hardware so that data retrieval is more efficient.
 Physical level- Because changes in the hardware are required to accommodate
the new changes, then the physical level will be changed by the DBMS to suit
the new hardware
 Conceptual level- No changes are required at this level because the data model
does not change as the new application uses existing data
 External level- Some changes in the user views will have to be done to make it
possible for the new application to access only parts of the data that its
supposed to access

(iii) A new application uses tables already in the database that are currently in use by
different application processes; no existing application process uses all of these tables.
 Physical level- No change is required in the physical level because data physical
storage of the data is handled by the database management system (DBMS).
 Conceptual level- No changes required in the conceptual level because the data
model of the database does not need any changes as the new application will be
using existing tables
 External level- Some changes in the user views will have to be done to make it
possible for the new application to access only parts of the data that it’s
supposed to access
Part 4 Data Modelling
Question 4
(a) List entity types and relationship types;
Entity Types
 Bird ( birdID, commonName, biologicalName, size, colouring, picture,
habitatDescription,
familyID)
 Family ( familyID, name, generalCharacteristics )
 Location (locationID, name, description, GPS_Coordinates)
 Observation (observationID, date, time, duration, locationID, notes)
 Observed_birds (observationID, birdID)
Relationship Types
 Bird one-to-one family
 Family one-to-many bird
 Location one-to-one observation
 Observation one-to-one location
 Observation one-to-many optional observed_birds
 Observed_birds one-to-one observation
(b) Based on the diagram

(i) The number of relationships is 4
2 entities of entity type Able are involved in the relationships while 4 entities of entity
type baker are involved in the relationship.
(ii) Sentences describing relationship and degree of multiplicity and participation
 Entity Type Able A1 affects one and only one entity type Baker B3
 Entity Type Baker B3 is affected by one and only one entity type Able A1
 Entity Type A1 affects one or many of entity type Baker
 Entity type Baker B1 is affected by one and only one Entity Type Able A2.
 Entity type Baker B4 is affected by one and only one Entity Type Able A2.
 Entity type Baker B5 is affected by one and only one Entity Type Able A2.
(iii) Entity relationship diagram
(iv) The above information is important when working with stakeholders to develop a
database because it helps the type of relationships and the degree of multiplicity and
participation
(c) Relationships based on figure 3
 Each driver may win none or many races
 Each race is won by one and only one driver
 Each driver may compete in none or many races
 Each race is competed for by many drivers
 Each driver may finish none or many races
 Each race is finished by none or many drivers.
(d) For each overlooked attribute, each of the following location is applicable
(i) The cost of each product from each supplier- This attribute is supposed to be stored in
the intersection entity formed by decomposing the relationship type supplier.

(ii) The address of the supplier- This attribute is supposed to be stored in supplier entity
type
(iii) The attribute called capacity is supposed to be stored in an intersection entity type
formed by decomposing the relationship type supplies
(iv) The attribute called price is supposed to be stored in the product entity type.
Question 5
(a) Primary keys and foreign keys
(i) A primary key is a unique attribute that is used to identify other non-key attributes in a
table. Each row in the table is supposed to have a unique key. A foreign key is a key that
is found in the child table of a relationship between a parent table and a child table. A
foreign key references the primary key of a record in the parent table.
(ii) Foreign keys and the referential integrity rule are important in representing
relationships within a database because they help ensure consistency in the data.
Consistency in the data helps eliminate insertion anomaly, update anomaly and delete
anomaly where by consistency of data in the parent and child tables is supposed to be
maintained.
(b) Conceptual model
(i) Relation-for-relationship relational representation
(ii) Posted key relational representation

Part 5: SQL
Question 6
(a) Building firm question
(i) How amendment or update anomaly might arise
An update anomaly occurs as a result of a partial update on redundant data thus causing
data inconsistency. For example considering the contract relation in table 1 an update
anomaly can occur when the contractor code for Building JLB2 needs to be updated. If
the user does not realize that JLB2 is repeated three times thus the contractor code will
have to be updated three times then the update will cause an update anomaly in the
table.
(ii) Contract is in first normal form because repeating groups have been eliminated
resulting to redundant data rows as seen in the table.
(iii) New relations in 2nd normal form are;
 Building (Building, customerName)
 Contractor (contractorCode, contractorName, Trade, chargeRate)
 Contract(Building, contactorCode, startDate, HoursWorked)
(iv) The relations in step ii above are all in 3rd normal form except for the contractor relation.
 Contractor (contractorCode, contractorName, Trade, chargeRate)
This relation is not in 3NF because there exists a transitive dependency in the relation
because TradechargeRate.
(b) Converting relational representation to SQL DDL
Create table event (
referenceCode EventCodes PRIMARY KEY,
date Date not null UNIQUE,
Location Venues NOT NULL,
companyNames Names NOT NULL,
foreign key (companyName) references Supporter (companyName)
);
Create table supporter(
CompanyName Names PRIMARY KEY,
contactAddress Addresses NOT NULL,
SpecialInterests Interests NOT NULL
);
Question 7
(a) SQL Statements
(b) The request answered by the SQL Query is;
The query gets the average quantity for each classification from Spain or Ireland.
(c) Logical processing model

The logical processing of the query involves the following steps;
 The first step is to select all countries and count the years
 The next step is to limit the results using the where condition
 The next step is to group the results by country
 The last step is to limit the results using the having condition.
(d) The capital city of Austria is Vienna, with a population of 1 794 000. Add this information to the
city table using an SQL INSERT statement
(e) The difference between inner join and outer join is that inner join matches keys on both tables
while outer join matches keys on only one table. For example considering table A and B
performing an inner join on A and B returns an intersection of all the rows in the two tables
while a left outer join on A and B will return all rows in A and any matching rows in B while a
right outer join on A and B will return all rows in B and any matching rows in A while a full outer
join returns all the rows in both tables.
(f) An economist has defined the world’s wealthiest countries as those where the GDP is at least
500*109 ECUs. Create an SQL VIEW with the name wealthiest that selects from the country
table those rows that meet the economist’s definition. The VIEW should include the values from
name, capital, area and population in the country table, giving them the names country, capital
city, area and population (respectively) in the VIEW.
Question 8
(a)
(i) Applying the principle of least privilege will help determine appropriate rights for
different users because each user will only be allowed to access data that they are
supposed to access meaning no user will have access data that he or she is not involved
in. By creating different privileges for each type of user, each user is limited only to the
privileges that falls in his category thus minimizing the risk of accessing data that a user
is not supposed to access.
(ii) For a database developer there are two ways to control access to the data. This is
through;
 Users- A user is created and assigned different privileges. For example the root user
can have all privileges while the HR user can be created to only access data that is
needed by HR department.
 Roles- A role is a group of privileges that can be assigned to a user. For example a
HR role can be created with all the privileges needed by a HR user and the role can
be assigned to a HR user.
By creating users and roles, the database developer is able to control access of the database
because a user is only limited to the privileges available under the user or the role assigned
to the user.
(b) F
(i) The seat_reservation and ticket_issue processes led to Betty receiving tickets she had’nt
confirmed because it’s possible the database has no locks thus when the seat
reservation process executed its possible the ticket issue process executed and made a
commit before the seat reservation could perform a rollback that the reservations were
committed as issued thus resulting to Betty receiving the tickets

(ii) Locks are very important for concurrent access and could have helped manage the
problem above because if a process was capable of locking the data during the
transactions it means the other process could not have accessed the data until the first
process released the data from the lock. Based on the problem above, concurrent
access using locks would have been implemented by giving seat_reservation process a
lock on the data. This means that the ticket_issue would have waited for the
seat_reservation process to release the data from the lock thus the roll back would have
been successful.
(c) ACID properties with examples
 Atomicity- this property entails that either a transaction takes place completely or does
not happen at all. The transaction is not supposed to take place partially meaning that if
the transaction aborts then changes made to the database should not be visible while if
the transaction commits changes made to the database should be visible. For example
considering an airline seating application if a seat reservation is committed then a new
record should exist in the database but if its aborted then no record of the reservation
should exist in the database.
 Consistency- Database consistency should be maintained before and after the
transaction is complete for example for an airline seating application booking a seat
means that the seat should not be available for booking if the booking is successful.
 Isolation- Each transaction should execute in isolation allowing multiple transactions to
occur simultaneously without causing database inconsistency. For example for an airline
seating application two reservations can be made concurrently but should not book the
same seat.
 Durability- If a transaction is committed and completes execution, the changes to the
database should be written to disk making them permanent. For example if a seat is
booked the changes in the airline seating database should be written to disk making the
booking permanent unless updated.