Database Design

Tutorial One: Relational data model concepts

1. What is a data model?

A data model is a formal representation of data that describes how data is related, structured, and used. It defines the data elements and the relationships between them, as well as the constraints and rules that govern the data. Data models are used to design databases, inform data analysis, and provide a blueprint for understanding and managing data.

2. What are the main categories of data models? To which category does the Relational model belong?

The main categories of data models are:

1. Hierarchical Model
2. Network Model
3. Relational Model
4. Object-Oriented Model
5. Entity-Relationship Model
6. Document Model
7. Semantic Model

The Relational Model belongs to the Relational Model category.

3. a) Explain the following terms in the context of the relational data model:

Relation

Domain

Attribute

Tuple

Degree of a relation

The cardinality of a relation

Relation: A relation is a two-dimensional table of data representing the relationship between two or more entities.

Domain: A domain is a set of valid values that can be stored in a particular column of a table.

Attribute: An attribute is a single piece of data associated with an entity, such as a person’s age or a product’s price.

Tuple: A tuple is a single row in a relation, consisting of one or more attributes.

 Degree of a relation: The degree of a relation is the number of attributes or columns in a relation.

The cardinality of a relation: The cardinality of a relation is the number of tuples or rows in a relation.

1. b) Use the Suppliers-Parts database in Appendix1 to provide examples of each

Create a database name of suppliers_and_parts in this database create three table

Accordingly Supplier, Part, SupPart

The supPart table is dependent on the Supplier or Part.

— Create a database

CREATE DATABASE suppliers_and_parts;

 

— Select the database

USE suppliers_and_parts;

 

— Create a Supplier Table

CREATE TABLE supplier (

sid INT AUTO_INCREMENT PRIMARY KEY,

sName VARCHAR (40),

status INT(11),

city VARCHAR(255));

 

— Create Part Table

CREATE TABLE part (

p_id INT AUTO_INCREMENT PRIMARY KEY,

pNmae VARCHAR(40),

colour VARCHAR(255),

weight INT(11),

city VARCHAR(255));

 

— Create SupPart Table

CREATE TABLE suppart (

s_id INT,

p_id INT,

qty INT(11),

FOREIGN KEY (s_id) REFERENCES supplier(s_id),

FOREIGN KEY (p_id) REFERENCES part(p_id));

Fetch Data from SupPart Table

 SELECT * FROM suppart

INNER JOIN supplier ON supplier.s_id = suppart.s_id

INNER JOIN part ON part.p_id = suppart.p_id;

4. a) Explain the following terms in the context of the relational model

Super key

Candidate key

Primary key

Foreign key

Alternate key

Composite key

Super Key: A super key is a set of one or more attributes or columns that can uniquely identify tuples or rows in a table.

Candidate Key: A candidate key is a set of attributes or columns that can uniquely identify tuples or rows in a table. It is a minimal super key meaning there are no extraneous attributes in the set.

Primary Key: The primary key is a candidate key chosen to uniquely identify each tuple or row in a table.

Foreign Key: A foreign key is an attribute column that is used to establish and enforce a link between two tables. It is a field in one table that is the primary key of another table.

Alternate Key: An alternate key is a candidate key that is not chosen to be the primary key. It is used to provide an alternate way to look up records in a table.

Composite Key: A composite key is a primary key composed of two or more attributes or columns. It is used when a single attribute is not sufficient to uniquely identify a tuple or row.

1. Use the Suppliers-Partsand Supplier-Parts-Projects databases in Appendix 1 to provide, where possible, an example of each term.

— Create a database

CREATE DATABASE suppliers_parts_projects;

— Select the database

USE suppliers_parts_projects;

— Create a Supplier Table

CREATE TABLE supplier (

sid INT AUTO_INCREMENT PRIMARY KEY,

sName VARCHAR(40),

status INT(11),

city VARCHAR(255));

— Create Part Table

CREATE TABLE part (

p_id INT AUTO_INCREMENT PRIMARY KEY,

pNmae VARCHAR(40),

colour VARCHAR(255),

weight INT(11),

city VARCHAR(255));

— Create Project Table

CREATE TABLE project (

j_id INT AUTO_INCREMENT PRIMARY KEY,

jNmae VARCHAR(40),

colour VARCHAR(255),

city VARCHAR(255));

— Create a Shipment Table

CREATE TABLE shipment (

s_id INT,

p_id INT,

j_id INT,

qty INT(11),

FOREIGN KEY (s_id) REFERENCES supplier(s_id),

FOREIGN KEY (p_id) REFERENCES part(p_id),

FOREIGN KEY (j_id) REFERENCES project(j_id));

Fetch Data from Shipment Table

SELECT * FROM  shipment

INNER JOIN supplier ON supplier.s_id = shipment.s_id

INNER JOIN part ON part.p_id = shipment.p_id

INNER JOIN project ON project.j_id = shipment.j_id;

5. (optional) State the properties of a relation.

 Reflexive property: A relation is said to be reflexive if a = a for every element a in the set.

5. Symmetric property: A relation is said to be symmetric if a = b implies b = a for all elements a and b in the set.
6. Transitive property: A relation is said to be transitive if a = b and b = c implies a = c for all elements a, b, and c in the set.
7. Anti-symmetric property: A relation is said to be anti-symmetric if a = b and b = a implies a = b for all elements a and b in the set.
8. Connectivity property: A relation is said to be connected if for any two elements a and b in the set, there are finitely many paths from a to b in the relation.

 

6. a) List the various cases where the use of a ‘null’ value would be appropriate?

 

1. When a value is not available or applicable.
2. When initializing a field value.
3. When representing the absence of a value.
4. When representing a missing value in a database table.
5. Is the primary key (or any of its components) allowed to accept null values, and why? What is the term for this constraint?

No, primary keys or any of their components are not allowed to accept null values. This is because a primary key is used to uniquely identify a record in a table and allowing null values would mean that multiple records would have the same identifier. This constraint is referred to as the NOT NULL constraint.

7. a) Describe the constraint called referential integrity

Referential integrity is a type of database constraint that ensures the accuracy and consistency of data in a relational database by enforcing a link between two or more tables. It ensures that the foreign key element in one table that refers to the primary key in another table in one table always matches the primary key in another table. This prevents orphan records in a child table that are not related to any record in the parent table from occurring. It also ensures that if the primary key in one table is changed or deleted, the corresponding foreign key in the other table is changed or deleted as well.

1. Specify which attribute reference which attributes on the attached schema of the Suppliers-Parts, and Supplier-Parts-Projects databases

In the Suppliers–Parts database, there are three relations (tables) Supplier, Part, and SupPart

Supplier and Part tables are the parent tables of the SupPart table.

1. The Supplier table have hold the information about Supplier .S# in the supplier table is the primary key, which identifies the suppliers in the supplier table. The other attributes like sName, status, and city hold the other information about Supplier.
2. The Part table have hold the information about the Parts. P# in the supplier table is the primary key, which identifies the parts in the Part table. The other attributes like pName, color, weight, and city hold the other information about Parts.
3. These two tables Supplier or Part are the parent table of the SupPart table. SupPart table holds the information about the Supplier or Parts. The Primary Keys of the Supplier or Part are used as Foreign Keys in the SupPart table. In SupPart table, S# or P# are the reference keys that are attached by the Supplier or Parts table.

In the Supplier-Parts-Projects database, there are three relations (tables) Supplier, Part, Project, and Shipment. The shipment table is very dependent on the Supplier, Part, and Project table.

The S#, P#, and J# in the Supplier, Parts, and Project table is the Primary Key that identifies the suppliers, parts, and project details in the table. The other attributes in these tables hold the other information about the Supplier, Part, and Project.

The S#, P#, J# in the Shipment table are foreign keys that identify the data of these tables

Supplier, Part, and Project. The Foreign keys provide the reference of the data in the other tables of the database

1. Use the Suppliers-Parts database to discuss why it is desirable to enforce this constraint.

In the Supplier-Parts database, the SupPart table is very dependent on the Supplier

In addition, Part of the table. The Supplier and Part tables are the parent table of the SupPart. Table. Not all three tables individually hold the complete information of

Supplier or Parts. That is why this table is connected to the SupPart table to provide the whole information

8. (optional) The tables below are part of a database catalog, which provides detailed information about the objects in the database. The catalog uses informal relational names. Rename the tables and column headers using formal relational terms.

 

TABLES

TABNAME	COLCOUNT	ROWCOUNT	………
DEPT	3	3	………
EMP	4	4	………
………	………	………	………

 

COLUMNS

TABNAME	COLNAME	………
DEPT	DEPT#	………
DEPT	DNAME	………
EMP	EMP#	………
EMP	ENAME	………
EMP	DEPT#	………
………	………	………

 

 

Appendix 1

 

The Suppliers-and-Parts Database

 

SupPart S# P# Qty S1 P1 300 S1 P2 200 S1 P3 400 S1 P4 200 S1 P5 100 S1 P6 100 S2 P1 300 S2 P2 400 S3 P2 200 S4 P2 200 S4 P4 300 S4 P5 400 S5 P4 200

Supplier

S#	SName	Status	City
S1	Smith	20	London
S2	Jones	10	Paris
S3	Blake	30	Paris
S4	Clark	20	London
S5	Smith	30	Athens

 

Part

P#	pName	Color	Weight	City
P1	Nut	Red	12	London
P2	Bolt	Green	17	Paris
P3	Screw	Blue	17	Rome
P4	Screw	Red	14	London
P5	Can	Blue	12	Paris
P6	Cog	Red	19	London

 

 

 

 

 

Suppliers-Parts Database Schema

 

Supplier

S#

sName

status

city

 

Part

P#

pName

color

weight

city

 

SupPart         

S#

P#

Qty

 

 

The Suppliers-Parts-Projects Database

 

Shipment S# P# J# Qty S1 P1 J1 200 S1 P1 J4 700 S2 P3 J1 400 S2 P3 J2 200 S2 P3 J3 200 S2 P3 J4 500 S2 P3 J5 600 S2 P3 J6 400 S2 P3 J7 800 S2 P5 J2 100 S3 P3 J1 200 S3 P4 J2 500 S4 P6 J3 300 S4 P6 J7 300 S5 P2 J2 200 S5 P2 J4 100 S5 P5 J5 500 S5 P5 J7 100 S5 P6 J2 200 S5 P1 J4 100 S5 P3 J4 200 S5 P4 J4 800 S5 P5 J4 400 S5 P6 J4 500

Supplier

S#	sName	status	city
S1	Smith	20	London
S2	Jones	10	Paris
S3	Blake	30	Paris
S4	Clark	20	London
S5	Smith	30	Athens

 

Part

P#	pName	color	weight	City
P1	Nut	Red	12	London
P2	Bolt	Green	17	Paris
P3	Screw	Blue	17	Rome
P4	Screw	Red	14	London
P5	Can	Blue	12	Paris
P6	Cog	Red	19	London

 

Project

J#	jName	City
J1	Sorter	Paris
J2	Display	Athens
J3	OCR	Athens
J4	Console	Athens
J5	RAID	London
J6	EDS	Oslo
J7	Tape	London

Suppliers-Parts-Projects Database Schema

Supplier

S#

sName

status

City

 

Part

P#

pName

color

weight

City

 

        Shipment

S#

P#

J#

Qty

Project

J#

jName

City