Transcript databases

Relational Databases
Why Databases
●
●
●
As programs get larger, data
becomes more important
Data is entities and
relationships
Real-world processes involve
complex data interaction
Structure of a Database
●
Record – one discreet entity
●
Field – one piece of data
●
Table – series of like records
Record
●
Represents some entity
●
Consists of many details
●
All details refer to same
record
Field
●
●
●
●
Specific data elements
Has a name, length, type,
value
Sometimes length is assumed
Each record has same field
definitions, but different
Table
A series of records following
the same definition
● All records should describe
same kind of thing
● Each record is a different
instance of that thing
●
Database
●
●
●
A series of tables each
defining different entities
The relationships between
those entities
Ancillary files, indices, views
Primary Keys
●
●
A primary key is a field or group of fields that
uniquely identifies a record. A primary key
comprised of two or more fields is called a
composite primary key. Every table must have a
primary key!
The most important key in a table:
–
–
–
–
Uniquely identifies a specific record throughout a
database
Identifies a specific table throughout the database
Enforces table-level integrity
Helps to establish relationships between tables
Foreign Keys
●
●
●
A foreign key is important when establishing
relationships between tables.
To create a foreign key, you would take a
primary key from one table and incorporate it
in a second table. In the second table, the
key becomes a foreign key.
Foreign keys enforce relationship-level
integrity – values in one table's foreign key
field must match exactly with the
corresponding values of a second table's
primary key field.
Table Relationships
●
We categorize table relationships in
the Relational Model as follows:
– One-to-One
(1:1)
– One-to-Many (1:N)
– Many-to-Many (N:N)
●
To establish a relationship between
tables, we need to match values of
a shared field.
One-To-One Relationships
(1:1)
●
●
●
A record in one table (a parent table) is related to
one and only one record in a second table (a child
table). A record in a second table (the child table) is
related to one and only one record in the first table
(the parent table).
We create a 1:1 relationship by copying the primary
key of a parent table into a child table, where it
becomes a foreign key.
This type of relationship is unique because both
tables share the same primary key. The primary key
in the child table serves both as that table's primary
key and a foreign key .
One-To-Many Relationships
(1:N)
●
●
●
A record in one table (a parent table) can be
related to many records in a second table (a
child table). A single record in the child table
is related to one and only one record in the
parent table.
We create a 1:N relationship by copying the
primary key of a parent table into a child
table, where it becomes a foreign key.
This type of relationship is the most common
type of relationship in the relational database
model.
Many-To-Many Relationships
(N:N)
●
●
A record in one table can be related to many
records in a second table. A single record in
the second table can be related to many
records in the parent table.
We create a N:N relationship by copying the
primary keys of a each table into a third
table, called a linking (associative) table.
Together, the copied keys form a composite
primary key. Individually, they serve as
foreign keys for the other table.
Example of N:N
Relationship
SQL
●
●
●
Structured Query Language
Uniform system for defining,
querying data
Used by many programming
languages and db tools