Scribd
Upload
67 views14 pages

Introduction-to-Database-and-SQL-Week 03-Fall-2022

The document discusses the relational database model and its key concepts. It defines the logical and physical structures of data and describes primary and foreign keys. Primary keys uniquely identify rows while foreign keys link tables by referencing a primary key. The document also covers one-to-one, one-to-many, and many-to-many relationships and provides examples. It defines data redundancy as unnecessary duplicate data and discusses implications like inconsistency, poor security, and anomalies during updates, insertions, and deletions.

Uploaded by

Shivam Patel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
67 views14 pages

Introduction-to-Database-and-SQL-Week 03-Fall-2022

The document discusses the relational database model and its key concepts. It defines the logical and physical structures of data and describes primary and foreign keys. Primary keys uniquely identify rows while foreign keys link tables by referencing a primary key. The document also covers one-to-one, one-to-many, and many-to-many relationships and provides examples. It defines data redundancy as unnecessary duplicate data and discusses implications like inconsistency, poor security, and anomalies during updates, insertions, and deletions.

Uploaded by

Shivam Patel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Relational Database Model

• Logical structure of data


• Can be the conceptual structure of data
• Cannot be physical structure of the data
Keys

• Keys are important in a relational model


• Two main types of keys:
• Primary Key
• Foreign Key
Primary Key
• The PRIMARY KEY constraint uniquely identifies each row in a
table. CUSTOMER
Customer_ID (PK)
First_Name
Last_Name
Gender
Email_id

Customer_ID (PK) First_Name Last_Name Gender Email_id


1 Mary Gold F [Link]@[Link]
2 Jane Smith F [Link]@[Link]
3 Susy Summers F [Link]@[Link]
4 Jean Grey F jean .grey@[Link]
5 Tom Brown M [Link]@[Link]
6 Sam Chang M sam. chang@[Link]
7 Maria Cruz F [Link]@[Link]
Foreign Key
• A FOREIGN KEY is a key used to link two tables together. A primary
key from one table is used to join to another table forming a parent
child relationship.
CUSTOMER ORDERS
Customer_ID (PK) Order_ID (PK)
First_Name Customer_ID (FK)
Last_Name Order_Desc
Gender Order_Qty
Email_id Unit_Price
Order_ID (PK) Customer_ID (FK) Order_Description Order_Qty Unit_Price
1 1 Apparel 4 50.26
2 2 Power Tools 3 45.12
3 2 Books 3 45.5805
4 4 Electronics 550 16.086
5 4 Furniture 3 57.0255
6 5 Office Supplies 100 37.086
7 6 Hardwood 5 26.5965
8 3 Toys 550 27.0585
Integrity Rules
Relationships

• Graphical representation of how an entity’s data is related


to another entity.
• One to one relationship
• One to many relationship
• Many to many relationship
One to One relationship
• In a One to One relationship between two tables, a row in one
table can only relate to one row in the table on the other side
of their relationship and vice versa.

Student Student_ID

Person Social_Insurance_number

Phone IMEI
One to many relationship
• In a one-to-many relationship, one record in a table can be
associated with one or more records in another table.

Customer Orders

Patient Hospital_visits

Customer Orders
Many to many relationship
• A many-to-many relationship occurs when multiple records in
a table are associated with multiple records in another table.

Books Authors

Books Authors

Book_Author
Data Redundancy
• Unnecessarily storing same data at different places
• Islands of information: Scattered data locations
– Increases the probability of having different versions of
the same data
• Proper use of foreign keys minimizes data
redundancies
Data Redundancy Implications
• Poor data security
• Data inconsistency
• Increased likelihood of data-entry errors when
complex entries are made in different files
• Data anomaly: Develops when not all of the required
changes in the redundant data are made successfully
Types of Data Anomaly

Update Anomalies

Insertion Anomalies

Deletion Anomalies

You might also like