DBMS Fundamentals Introduction 1 What is a Database

DBMS Fundamentals: Introduction 1

What is a Database System • A Database System is essentially a computerized record-keeping system. • A database-management system (DBMS) consists of a collection of interrelated data and a set of programs to access those data. • Database systems are designed to manage large volume of information 2

File Organization : Terms and Concepts • Database: Group of related files • File: Group of records of same type Data Hierarchy in a Computer System • Record: Group of related fields • Field: Group of words or a complete number • Byte: Group of bits that represents a single character • Bit: Smallest unit of data; binary digit (0, 1) 3

Database Concepts: A Relational Approach 4

Database: Introduction A Database is an electronic store of data. q It is a repository that stores information about different things and also contains relationships among those different things. 5

Structure of database • A Person, place, event or item is called an entity. • The facts describing an entity are known as data. • Each entity can be described by its characteristics, which are known as attributes. Eg. Student identification number, last name, first name, phone number, gender and so on. • All the related entities are collected together to form an entity set. • A database is collection of entity sets. • The entities in a databse are likely to interact with other entities. The interactions between the entity sets are called relationships. 6

Relationships One-to-One E 1 E 2 1: 1 One-to-Many 1: M Many-to-Many E 1 E 2 M: N 7

Realtionships Ø One-to-one relationship § It is written as 1: 1 in short form § Eg. An employee manages one department in a company, and only one employee manages a department. Ø One-to-many relationship § It is written as 1: M. § Eg. (i)An Employee works in a department, but a department has many employees. § (ii) A faculty teaches for one division in a college, but division has many faculty members. 8

(cont. . ) Ø Many-many relationship: § It is written as M: N or M: M. § Eg. (i)An employee works on many projects, and a project has many employees. § (ii) A student takes many courses, and many students take a course 9

Database Management System(DBMS) • A DBMS software package such as MS-Access, Visual Foxpro, MS-SQL server, Oracle. • A user developed and implemented database that includes tables, a data dictionary. • Custom applications such as data-entry forms, reports, queries, blocks and programs. • Computer hardware: Personal computers, mini computers and mainframes in a network environment. • Software: An operating system and a N/W OS. • Personnel: A database administrator, a database designer/analyst, a programmer and end users. 10

Fig: Database System User Applications DBMS Database OS Software Hardware 11

Important functions • The data dictionary is a system structure that stores metadata(data about data). • It manages all day-to-day transactions. • It secures access through passwords, encryption and restricted user rights. • It provides backup and recovery procedures for physical security of data. • It allows users to share data with data-locking capabilities. • It provides import and export utilities. • It allows users to specify validation rules. 12

Relational Database Model • Represents data as two-dimensional tables called relations • Relates data across tables based on common data element Examples: DB 2, Oracle, MS SQL Server 13

Relational database Model • A row is referred to as a tuple. • A Key is a minimal set of columns used to uniquely define any row in a table. • A single column is used as unique identifier, it is known as primary key. • A column in a table that references a column in another table is known as foreign key. 14

Relational database Model • PROJ 2017 Proj. No Location Customer 1 Ukkadam David 3 Gandhipuram John 5 Malumachampatti Kumar 6 Pollachi Dinesh 7 Townhall Lokesh • PROJ 2018 Proj. No Location Customer 1 Ukkadam David 2 Perur Allen 3 Gandhipuram John 4 RS Puram Edison 15

• PRJPARTS Proj. No Part No Qty 1 11 20 2 33 5 3 11 7 1 22 10 2 11 3 • PARTS Part. No Part. Desc Vendor Cost 11 Nut Prakash 19. 95 22 Bolt Madhesh 5. 00 33 Washer Arun 55. 99 16

• DEPARTMENT Dept. No Dept. Name 10 Production 20 Supplies 30 Marketing • EMPLOYEE Emp. No Ename Dept. No Proj. No Salary 101 Albert 10 1 25000 102 Bala 20 3 37000 103 Mathivanan 30 6 50500 104 Suresh 20 5 23700 105 Jagadeesh 10 7 75000 17

Notation used for tables • • • PROJ 2017(Proj. No, Location, customer) PROJ 2018(Proj. No, Location, customer) PRJPARTS(Proj. No, Part. No, Qty) PARTS(Part. No, Part. Desc, Vendor, Cost) DEPARTMENT(Dept. No, Dept. Name) EMPLOYEE(Emp. No, Ename, Dept. No, Proj. No, Salary) 18

Integrity Rules • Entity Integrity: Ø No column in a primary key may be null. ØA Null value means a value that is not known, not entered, not defined, or not applicable. • Referential Integrity: Ø A foreign key value may be a null value, or it must exist as a value of primary key in the referenced table. 19

Theoretical Relational Languages 1. Relational Algebra: A Procedural language Ø Ø Ø Ø Ø Union Intersection Difference Projection Selection Product Assignment Join Division 2. Relational Calculus: A nonprocedural language 20

Union TABLE_A=PROJ 2017 U PROJ 2018 TABLE_A Proj. No Location Customer 1 Ukkadam David 2 Perur Allen 3 Gandhipuram John 4 RS Puram Edison 5 Malumachampatti Kumar 6 Pollachi Dinesh 7 Townhall Lokesh 21

Intersection TABLE_B=PROJ 2017 ∩ PROJ 2018 TABLE_B Proj. No Location Customer 1 Ukkadam David 3 Gandhipuram John 22

Difference TABLE_C=PROJ 2017 - PROJ 2018 TABLE_C Proj. No Location Customer 5 Malumachampatti Kumar 6 Pollachi Dinesh 7 Townhall Lokesh TABLE_D=PROJ 2018 - PROJ 2017 TABLE_D Proj. No Location Customer 2 Perur Allen 4 RS Puram Edison 23

Projection • TABLE_E=PARTS(Part. Desc, Cost) Part. Desc Cost Nut 19. 95 Bolt 5. 00 Washer 55. 99 Selection • TABLE_F=Sel(PARTS: Cost>10. 00) Part. No Part. Desc Vendor Cost 11 Nut Prakash 19. 95 33 Washer Arun 55. 99 24

Product • If the first table has x rows and the second table has y rows, the resulting product has x*y rows. • If the first table has m columns and the second table has n columns, the resulting product has m+n columns. DEPARTMENT EMPLOYEE Dept. Name Ename Production Albert Supplies Bala Marketing 2 * 3=6 rows 1 + 1=columns TABLE_G=EMPLOYEE*DEPARTMENT Ename Dept. Name Albert Production Albert Supplies Albert Marketing Bala Production Bala Supplies Bala Marketing 25

Assignment • Assignment(=) gives us an ability to name new tables that are based other tables. • Eg: TABLE_A=PROJ 2017 U PROJ 2018 TABLE_C=PROJ 2017 - PROJ 2018 Join: TABLE_H=join(EMPLOYEE, DEPARTMENT: Dept. No=Dept. No) Emp. No Ename Dept. No Proj. No Salary Dept. Name 101 Albert 10 1 25000 Production 102 Bala 20 3 37000 Supplies 103 Mathivanan 30 6 50500 Marketing 104 Suresh 20 5 23700 Supplies 105 Jagadeesh 10 7 75000 Production 26

Division: It identifies rows in one table that have a certain relationship to all rows in another table. • Let us consider the following two tables: PROJ Proj. No 1 2 TABLE_I=PRJPARTS/PROJ 3 PROJPARTS Proj. No Part. No 1 11 2 33 3 11 1 22 2 11 Part. No 11 27

Database Design: Data Modeling and Normalization Data Modeling • The Entity-Relationship (E-R) model is a very popular modeling tool among many such tools available today. • The E-R model uses E-R diagrams(ERD) for graphical representation of the database components. • An entity (or an entity set) is represented by rectangle. The name of the entity(set) is written within the rectangle. 28

• A line represents relationship between the two entities. • The name of the relationship is an active verb in lowercase letters. • For Eg: works, manages, employs EMPLOYEE 29