Database Design
Tutorial One: Relational data model concepts
- 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.
- What are the main categories of data models? To which category does the Relational model belong?
The main categories of data models are:
- Hierarchical Model
- Network Model
- Relational Model
- Object-Oriented Model
- Entity-Relationship Model
- Document Model
- Semantic Model
The Relational Model belongs to the Relational Model category.
- 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.
- 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;
- 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.
- 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;
- (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.
- Symmetric property: A relation is said to be symmetric if a = b implies b = a for all elements a and b in the set.
- 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.
- 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.
- 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.
- a) List the various cases where the use of a ‘null’ value would be appropriate?
- When a value is not available or applicable.
- When initializing a field value.
- When representing the absence of a value.
- When representing a missing value in a database table.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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
- (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
|
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
|
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 |
Thanks for sharing. I read many of your blog posts, cool, your blog is very good.