Database Assignment - Database Design, ER Diagrams, and Normalization

This database assignment covers key concepts in database design and management. The assignment explores topics such as database schemas, relational models, entity-relationship (ER) diagrams, and normalization. Specific questions address database terminology, including strong and weak entities, attributes, and relationships. The solution includes examples of SQL queries and database design principles. The assignment also examines the use of primary and foreign keys, and the different normal forms. The questions test the understanding of functional and transitive dependencies, and the application of normalization rules. The assignment concludes with the application of these concepts in real-world scenarios.

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

1
DATABASE
Answer 1
A database is a shared collection of logically related data, and a description of this data, designed to meet the
information needs of an organization.
A database server is a dedicated computer system (hardware and software) which runs the DBMS, and contains the
database and underlying data.
MySQL Workbench is a popular DBMS which supports SQL.
Most application programs enable users to define, create, maintain, and control access to a database.
MySQL Server 5.7 is a popular relational DBMS which supports SQL.
Answer 2
A database schema is a description of the database structure. Data
independence makes each level immune to changes to lower levels.
refers to the immunity of the external schemas to changes in the conceptual
schema. refers to the immunity of the conceptual schema to changes
in the internal schema.
Answer 3
A row of a relation – a named column of a table
A table with columns and rows – Attribute
The set of allowable values for one or more attributes – Domain
A named column of a table – Row
The number of attributes in a relation – Degree
Database Schema
Physical Data Independence
Logical Data Independence

2
DATABASE
Answer 4
Strong entities have a distinct and independent existence.
An entity occurrence is a uniquely identifiable object of an entity type.
Strong entities modelled in an ER diagram become tables in the relational model.
Composite attributes are usually represented as relations in the relational model.
A relational schema derived from an ER model should be validated using the rules of normalization so
that it satisfies various constraints to avoid unnecessary redundancy.
Answer 5
Weak Entity types – 4
Composite Attributes – 1
Multi-valued Attributes – 1
Uniqueness Constrains – 2
Integrity Constrains – 0
Answer 6
Ternary

3
DATABASE
Answer 7
Employees
Answer 8
InvoiceNumber
Answer 9
The relationship between jobs and invoices
Answer 10
Option C.
Answer 11
Contents ( recipeID, ingredientsID, Contents)
Ingredients (IngrredientsID, name, despription, price)
Answer 12
Attributes that cannot be NULL as they participate in a Primary Key: 2
Attribute that are subject to a referential Integrity Constraint: 1
Answer 13
How many super key are there in the host relation: 1
How many candidate keys are there in the Bookings relations: 3

4
DATABASE
Answer 14
What is the most appropriate primary key of the Bookings relation?
What are the foreign keys in the Bookings relation? and
(Select the answers in alphabetical order)
Answer 15
invoiceNo
jobNo
amount
status
Answer 16
employeeNo
phoneNumber
name
gender
clientNo
Answer 17
5
bookingID
customerID hostID

5
DATABASE
Answer 18
Consider the Departments table and its tuples above from the Surf and Sun Clothing
relational schema.
What normal form is this table in? NF
If this table was normalised one step to the next normal form, how many tuples would
there be?
Answer 19
How many primary keys are there in the relation: 1
How many full functional dependencies are there in the relation: 4
How many transitive dependencies are there in the relation: 2
What is the highest normal form that the relation satisfies: 3 NF
0
9