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

COLUMNS

Appendix 1

The Suppliers-and-Parts Database

Supplier

Part

Suppliers-Parts Database Schema

Supplier

Part

SupPart         

The Suppliers-Parts-Projects Database

Supplier

Part

Project

Suppliers-Parts-Projects Database Schema

Supplier

Part

        Shipment

Project