Transcript VB Lecture 1 - American University of Beirut

CVEV 118/698
Databases
Lecture 1
Prof. Mounir Mabsout
Elsa Sulukdjian
Walid El Asmar
Introduction to Databases

A database (DB) is an electronic filing cabinet, a
collection of related data organized into a list.
Example: list of all VB experts at AUB, with their name,
age and phone number.

Such a list under any electronic format can be called
a database, I.e. you can construct a database on MS
Excel, Word or even Notepad as long as it is a
collection of related information.

However the use of a database program, such as MS
Access, will highly enhance the ability to store,
manage, find, share, analyze, print, etc…
information from your list(s).
DataBase Management System

“DBMS is to a database what a word processor is to a
letter. The former is the controlling, the latter the data
that it manipulates.” Whitehorn and Marklyn.

DBMS are developed for efficient access and control of
information in databases:
– Sort and group records.
– Issue complex queries.
– Managing/Simplifying interactivity to let the user concentrate
on higher-level issues.
NB: A query is a question, an inquiry. Queries ask a question on the
information in a list and then retrieve and display the result, I.e. What
projects were already corrected by the VB experts?
DBMS Functions

Functions of Database Management Systems:
– Controlling redundancy / sharing of data.
– Restricting unauthorized access.
– Representing complex relations among data.
– Enforcing integrity constraints.
– Backup and recovery facilities.
DBMS History

Flat file

Proprietary DBMS

Relational DBMS
Flat File



Data entries were just lumped in order into text
files.
Delimitation was often done by commas or
simply spaces.
Associated problems:
– Scalability
– Accuracy
– Expandability
Proprietary DBMS



Core code originally developed for a general
scope.
Addenda on the core code to adapt to specific
projects.
Associated problems:
– Integration with other products
– Ease of usage and efficiency of output
Relational DBMS

RDBMS distinguishes itself by the way data is
organized in it in tabulated forms (a relation is a
table).
RDBMS stars: Oracle, MS Access, MS SQL, Sybase.

Terms used:

– Entity: place, person, event of which you want to keep
facts (row, record).
– Attribute: categorized fact of an entity (field, column).
– Entity Set: group of entities (table, relation, file).
Example

The order of engineers wants a database that
manages engineers, universities they graduated
from, and the projects they will work on during
their career.
Example (Cont’d)

Facts to consider:
– An engineer can graduate from only one university.
– A university can graduate more than one engineer.
– A project can have more than one engineer.
– An engineer can have more than one project.
Example Data

Engineers:
Mayon Abou Zeid, AUB, BE, 2001
 Laron Amm, LAU, BE, 2002
 Rayon Bsat, ESIB, BE, 2003


Projects:

Tabbouleh
– Mayon April 2001 - June 2001
– Rayon June 2001 – April 2002

Chocolate
– Laron October 1997 - June 2001

Kebbeh
– Rayon April 2001 - May 2001
– Laron September 2001 - April 2002
DB Design

There are many ways to model the database of a
certain problem such as this example.

Important elements in the design frame:
– Keys.
– Integrity constraints.
– Relations between tables.
Primary Key


Primary key is a field that uniquely references a
record in a table.
In our example, to reference to a certain engineer,
we need to check his identity by looking at one or
several of his attributes; I.e.:
– Security Number; Family name + Date of birth; etc.

Or we can create a field on its own, usually
containing numerical values, to provide each
entry/engineer with a unique identity, I.e. ENGID.
Primary Key (Cont’d)



Creating a primary key (PK) in a table is a very encouraged
practice, especially when dealing with multiple-table
databases.
This will help in connecting and managing information
within the project.
Thus, in our example, we will most probably design 3
tables (Engineer, University and Project), with a PK each:
– Engineer Table
– University Table
– Project Table
ENGID
UNIVID
PJTID
Relationships

Relationships among records in DB tables can
be any of the following:
– One to One 1:1
– One to Many 1:M
– Many to Many M:M
Relationships in Our Example


An engineer can only graduate from ONE
university; but a university can have MANY
graduate engineers. Therefore, the relationship
between the two records is:
University
1 -- Many
Engineer
Similarly, we will have the following relation for
engineers and projects:
Engineer
Many -- Many Project
Foreign Key FK


It is a field identical in attribute to the primary
key of another table.
I.e. in our example, we can include in the
Engineer Table a field called UNIVID that
stores the same values as the primary key of the
University Table. This field is then a Foreign
Key.
Entity/Referential Integrity

A table exhibits Entity Integrity if the Primary
Key:
– Is unique for each record
– AND is NOT NULL

A database exhibits Referential Integrity if the
Foreign Key:
Matches the primary
 OR is NULL

E/R Diagram



Entity Relationship Diagram
Widely used to visualize DB Design
Table
University
UNVID
UNVName
Engineer
Project
ENGID
ENGFName
ENGLName
PJTID
PJTName
Example ER Diagram
University
UNVID
UNVName
Engineer
1
M
ENGID
ENGFName
ENGLName
UNVID
DegDate
Degree
EngProject
1
M
EPTID
ENGID
PJTID
StartDate
EndDate
Project
M
1
PJTID
PJTName
What’s Next

MS Access Forms, Queries, etc.