CVEV 118/698 Databases

1
CVEV 118/698Databases

• Lecture 1

Prof. Mounir Mabsout Elsa Sulukdjian Walid El
Asmar
2
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).

3
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?

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

5
DBMS History

• Flat file
• Proprietary DBMS
• Relational DBMS

6
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

7
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

8
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).

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

10
Example (Contd)

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

11
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

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

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

14
Primary Key (Contd)

• 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 ENGID
• University Table UNIVID
• Project Table PJTID

15
Relationships

• Relationships among records in DB tables can be
  any of the following
• One to One 11
• One to Many 1M
• Many to Many MM

16
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

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

18
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

19
E/R Diagram

• Entity Relationship Diagram
• Widely used to visualize DB Design
• Table

20
Example ER Diagram
Engineer
EngProject
ENGID ENGFName ENGLNameUNVIDDegDateDegree
EPTID ENGID PJTID StartDate EndDate
M
1
M
1
M
1
21
Whats Next

• MS Access Forms, Queries, etc.