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

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

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

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. Engineer Project University ENGID ENGFName ENGLName PJTID PJTName UNVID UNVName E/R Diagram • Entity Relationship Diagram • Widely used to visualize DB Design • Table

20. University Project UNVID UNVName PJTID PJTName Example ER Diagram Engineer EngProject ENGID ENGFName ENGLNameUNVIDDegDateDegree 1 M EPTID ENGID PJTID StartDate EndDate M 1 1 M

21. What’s Next • MS Access Forms, Queries, etc.