...
<linearGradient id="sl-pl-stream-svg-grad01" linear-gradient(90deg, #ff8c59, #ffb37f 24%, #a3bf5f 49%, #7ca63a 75%, #527f32)
Loading ...

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.

  1. 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.

  1. 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;

  1. 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;

  1. (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.

  1. Symmetric property: A relation is said to be symmetric if a = b implies b = a for all elements a and b in the set.
  2. 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.
  3. 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.
  4. 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.

 

  1. 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.

  1. 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 SuppliersParts 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

  1. (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

 

Share your love

Leave a Reply

Your email address will not be published. Required fields are marked *

Translate »
Verified by MonsterInsights
Seraphinite AcceleratorOptimized by Seraphinite Accelerator
Turns on site high speed to be attractive for people and search engines.