Lesson 1

Introduction to Database
Terms
• A Database is a structured collection of records
or data which are logically related.
• A Database Management System(DBMS) is
computer software designed for the purpose of
managing databases. Typical examples of DBMSs
include Oracle, Ms SQL Server and MySQL.
• A Relational Database Management System
(RDBMS)is a database management
system(DBMS) that is based on the relational
model.
• Structured Query Language (SQL) is the standard
language for interacting with an RDBMS.
SQL Server
• SQLServer is owned by Microsoft Corp.
• In 1988, Microsoft released its first version of
SQL Server.
• Microsoft's philosophy of combining a high-
performance database with an easy-to-use
interface proved to be very successful.
Microsoft quickly became one of the most
popular vendor of high-end relational
database software.
Components
Relational database components include a table, a
row, a column, a field, a primary key and a foreign
key.
Table
Row
Column
Field
Primary Key
Foreign Key
Tables
• A table is a basic storage structure of an
RDBMS and consists of columns and rows.
• A table represents an entity. For example, the
employee table stores information about the
employees of an organization.
Rows
• A row is a combination of column values in a
table and is identified by a primary key. Rows
are also known as records.
Columns
• A column is a collection of one type of data in
a table. Columns represent the attributes of
an object.
Fields
• A field is an intersection of a row and a
column. A field contains one data value. If
there is no data in the field, the field is said to
contain a NULL value.
Primary Keys
• A primary key is a column or a combination of
columns that is used to uniquely identify each
row in a table.
• A primary key must contain a value. It cannot
contain a NULL value.
Foreign Keys
• A foreign key is a column or set of columns
that refers to a primary key in the same table
or another table.
• A foreign key must either match a primary key
or else be NULL.
Properties of Relational Tables
• Column values are of the same kind
• Each row is unique
• The sequence of columns is insignificant
• The sequence of rows is insignificant
• Each column must have a unique name
Entity Relationship Diagrams (ERD)
• ERD is a design tool
• ERD graphically represents the logical
relationships of entities (or objects) in order to
create a database
• ERD is DBMS independent
• ERD has many variants
• ERD is composed of entities, attributes, and
relationships
Entity
• An entity is any object in the system that we want
to model and store information about.
• Individual objects are called entities
• The Entity will become a Table in the database.
• Entities are represented by rectangles (either
with round or square corners)
Attribute
• All the data relating to an entity is held in its
attributes.
• An attribute is a property of an entity.
• They appear inside ovals and are attached to
their entity.
Keys
• A primary key is a data item that allows us to
uniquely identify individual occurrences
• A composite key is a key that consists of two or
more attributes
• The name of each primary key attribute is
underlined.
• A foreign key is an attribute (or group of
attributes) that is the primary key to another
relation.
• Foreign keys are usually shown in italics or with a
wiggly underline.
Relationships
• A relationship is an association of entities.
• Relationships are represented on the ER
diagram by a series of lines.
Cardinality
The cardinality indicates the maximum number of
relationships between the entities, therefore "many"
can be explained as "one or many" or sometimes "zero,
one, or many".
Ordinality / Optionality
• Some diagrams will indicate the ordinalityof a relationship.
This will indicate whether the relationship is mandatory or
optional.
• The optionality can be different at each end of the
relationship
• For example, a student must be on a course. This is
mandatory. To the relationship `student studies course' is
mandatory.
• But a course can exist before any students have enrolled.
Thus the relationship `course_is_studied_by_student' is
optional.
• To show optionality, put a circle or `0' at the `optional end'
of the relationship.
• One course is studied by how many students? Answer =
‘zero or more’.
– This gives us the degree at the ‘student’ end.
– The answer ‘zero or more’ needs to be split into two parts.
– The ‘more’ part means that the cardinality is ‘many’.
– The ‘zero’ part means that the relationship is ‘optional’.
– If the answer was ‘one or more’, then the relationship would be
‘mandatory’.
• One student studies how many courses? Answer = ‘One’
– This gives us the degree at the ‘course’ end of the relationship.
– The answer ‘one’ means that the cardinality of this relationship
is 1, and is ‘mandatory’
– If the answer had been ‘zero or one’, then the cardinality of the
relationship would have been 1, and be ‘optional’.
Splitting n:m Relationships
• A many to many relationship in an ER model
can be replaced using an intermediate entity.
• This should be done where:
– the m:n relationship hides an entity
– the resulting ER diagram is easier to understand.
Creating an ERD
• Identify Entities
• Find Relationships
• Draw Rough ERD
• Fill in Cardinality
• Define Primary Keys
• Draw Key-Based ERD
• Identify Attributes
• Map Attributes
• Draw fully attributed ERD
• Check Results
Rough ER Diagram Example
Practice Example 1
A company has several departments. Each
department has a supervisor and at least one
employee. Employees must be assigned to at least
one, but possibly more departments. At least one
employee is assigned to a project, but an employee
may be on vacation and not assigned to any project.
The important data fields are the names of the
departments, projects, supervisors and employees,
as well as the supervisor and employee number and
a unique project number.
Table Instance Chart
Along with a database diagram you use a table
instance chart. This chart shows the details of
one table, it’s columns, keys and data types.