1 Lecture 1 PAPER-OM-3 (Database Management System (DBMS) (MS Access):- WHAT IS DATABASE:- It is collection of well-organized related records in coherent manner. These records stored in the form of table. It consists of rows and columns. Rows are known as tuples and columns are known as attributes. Example:-Employee records, Students records, telephone Directory, Inventory control, Patients records, etc. EMP (Table):- Eno Ename Job Sal Deptno 101 Ajay Steno 12000 10 102 Vijay Manager 22000 20 103 Mukesh Clerk 10000 10 Eno, Ename, Job, Sal, Deptno Fields Name/Attributes Name/Column Name Number of Attributes=5 Number of Tuples=3 Cardinality of EMP table=3 Degree/Arity of EMP=5 DBMS:-It is an efficient system/Software by which we manage database. Example:-MS Access, Oracle, SQL Server, DB-2, Ingress, FoxPro etc. Functions of DBMS:-  Creation of New database.  Deletion of existing database.  Renaming database.  Updating database.  Searching of records.  Sorting of records.  Indexing of records.  Creation of View (Logical table).  Performing efficient query on database.  Applying constraints (validation rules) on database. Etc. Three View of Data:- 1. Logical View. 2. Conceptual View. 3. Internal View. Logical View:- EMP (Table) Eno Ename Job Sal Deptno Date_Of_Join Address Age 2 Conceptual View:- EMP (Table) Eno Number. Ename Text. Job Text. Sal Currency. Deptno Number. Date_Of_Join Date. Address Text. Age Number Internal View:- EMP (Table) Eno Number. 14 Ename Text. 25 Job Text. 18 Sal Currency. 20 Deptno Number. 10 Date_Of_Join Date. 8(Fixed Value) Address Text. 35 Age Number 5 Three Level Architecture Proposals for a DBMS:- 1. External level(Defined By User). 2. Conceptual Level/Global Level(Defined DBA). 3. Internal Level(DBA defined for optimization). View A View B View C View D Mapping/Relationship Supplied By DBMS Conceptual View Mapping/Relationship Supplied By DBMS/OS Internal View Types of DBMS USERS:- 1. Naïve Users. 2. Online Users. 3. Applications Programmers. 4. Database Administrators(DBA). Naïve users:- Users who need not be aware of the presence of the database system or any other system supporting their usage are considered naïve users. Online user:- These are users who may communicate with the database directly via an online terminal or indirectly via a user interface application programs.These Users Are Aware Of The Presence Of Database System and may have acquired a certain amount of expertise in the limited interaction they are permitted with the database thought the intermediately of the application program. 3 Application Programmers:- Professionals programmers who are responsible for the developing application programs or users interfaces utilized by the naïve and online users fall into this category. DBA:- A person or group of persons under the supervision of high-level administrator experts centralized control of the database. This person or group of person is referred to as the DBA. How To access Data from database:- Queries DBMS OS Data Base C/C++/C# Advantages of DBMS:- 1. Centralized Control. 2. Data independence allows dynamic changes and growth potential. 3. Data quality enhanced. 4. Security enforcement possible. Disadvantages of DBMS:- 1. Problems associated with centralization. 2. Cost of Software/Hardware and migration. 3. Complexity of backup and recovery. 4 Concept of E-R model (Entity –Relationship) model:- The entity-relationship model (or ER model) is a way of graphically representing the logical relationships of entities (or objects) in order to create a database. Entity:- It is an object, which is distinguishable from other object. Example:- Enrollment=02181050001. SB A/c number=234567. Passport Number=AB968754. Entity Set:- Collection of entity is called entity set.  Employee records table.  Students records table. Relationship (Mapping):- Relations between attributes of two entity sets is called mapping. There are following types of relationships. 1. 1:1(One: One) Mapping. 2. 1: M (One: Many) Mapping. 3. M: 1 (Many: One) Mapping. 4. M: N (Many: Many) Mapping. Example:- 1:1(One: One) Mapping  Relationship between Enrollment number and Student.  Relationship between Passport number and Person. 5 Example:- 1: M (One: Many) Mapping. A B Relationship between Manager and Employee. Manager Employee M: 1 (Many: One) Mapping. Example:- Relationships between Students and Teacher. Relationships between Vice Chancellor and Chancellor. Example:- A B Employee Projects 6 Symbols Used In E-R Model:- Entity Sets Attribute Associations Link Example of E-R Model:- Employee- Department Eno Ename Job Dname Deptno Employee Department Relatio n Head Address Deptno LOC Concept of Abstraction, generalization, Specilization and Aggregation:- Abstraction:- It is simplification mechanism used to superfluous details of a set of objects; it allows concentrating on the properties that are interest to the application. Example:-Car is an abstraction of a personal transportation vehicle. 7 Generalization:- It is the abstracting process of viewing sets of objects as a single general class. by concentrating on the general characteristics of the constituents sets suppressing or ignoring their differences. n o Employee i S at p z e i c al ia er li n z Ge atio n Full Time Part Time Employee Employee Faculty Staff Teaching Casuals Degree Interest Classifications Stipend Hour_rate Specialization:- It is the abstracting process of introducing new characteristics to an existing class of objects to create one or more new classes of objects. Aggregation:- It is the process of compiling information on an object, thereby abstracting a higher-level object. 8 Example:- Eno Ename Job Employee Address Deptno Sal MS Access: (Extension name .mdb) Data Types:- 1. Text (It accept characters and Numbers both size 0 to 255 characters) 2. Memo(For Large Amount Of text 0 to 64000 characters) 3. Hyperlink(for Linking and store 64000 characters) 4. OLE(For image) 5. Currency(For monetary Values) 6. Yes/No(For Boolean values true and false) 7. Autonumber(For generating automatic number) 8. Number (Accept only numbers) 9. Date/time size 8 characters Concept of Constraints (Validation rules/Business Rule):- 1. Primary key(Integrity Rule1)/ Minimum super key 2. Foreign Key/Reference Key(Integrity Rule2) 3. Not Null. 4. Unique Key. 5. Check constraints. 6. Default constraints. Primary key:- (Integrity Rule 1)/Minimum super key Column Value cannot accept null and duplicate values. Example:- Enrollment Number Primary key. Saving Bank A/c Number Primary Key. Passport Number Primary Key. Voter ID card Primary Key. PAN Card Number Primary Key. Etc. 9 Foreign Key/Reference Key: - (Integrity Rule2) Column value refered by column of master table (Main Table).There are two tables must be necessary. Foreign Key/Reference Key Primary Key Table T1 Table T2 Code2 FName Code1 Name 101 Vijay 101 Ajay 102 Ram Master Table Transaction Table 102 Raj 103 Rajan 103 Sanjay Not Null:- It can accept duplicate values but cannot accept null values. Unique Key:- It cannot accept duplicate but can accept null values. Check Constraints:- It accepts only predefined values during definition of tables. Example:- Check (city in (“VNS”,”ALD”,”Kan”,”Luck”,”Delhi”)). Default Constraints:- It may accept predefined default values. Example:- default sal(7000) How To Create Table:- Example:- Employee Database→Emp Table EMP (Eno, Ename, Job, Sal, deptno, City, Contact). Column Name Constraints Data Types Eno Primary Key Number Ename Not null Text Job Not Null Text Sal default Currency Deptno not null number City Not Null text Contact Unique number Method 1:- Creation of table using Design view. Method 2:- Creation of table using Wizard view It instructs and guide by means of dialogue boxes for creating new table. Lecture 2 Query (Questionnaires on database):- To access records from database is called query. There are following three techniques for performing query on database.  Access Design View.  Wizard.  SQL (Structure Query Language). 10 Operators used in query:-  Relational operators(>,<,>=,<=,<>)  Arithmetical Operators(+,-,*,/)  Predicate (and, or, not, like, not like, between, not between, in, not in, any, all, not all). Aggregate Functions Used in Query:- sum ( ) max ( ) min ( ) count ( ) avg ( ) Order Types:- 1. Ascending Order.(A-Z). 2. Descending Order.(Z-A). Wild cards:- 1. * For all Characters. 2. ? For any one characters. SQL Method of Query:- Select <col1>, <col2>, <col3>… from <table_Name> Where <Criteria_Expression> group by <Aggregate_Column_name> Order by <Column_name> asc|desc; Example:- Consider a table Emp (empno, ename, job, sal, deptno, city). 1:- To display all employee records. Select * from emp; 2:- To display empno, ename ,job salary only. Select empno, ename, job,sal from emp; 3:- To display employee records who is found salary more than 15000. Select empno, ename, job, Sal from emp where sal>=15000; 4:- To display employee records who is found salary between 10000 and 20000. Select empno, ename, job, Sal from emp where sal between 10000 and 20000; 5:- To display employee records whose job in clerk, steno, salesman or peon. Select empno, ename, job, Sal from emp where job in (‘clerk’,’steno’,’salesman’,’peon’); 6:- To display employee records whose job not exist in clerk, steno, salesman or peon. Select empno, ename, job, Sal from emp where job not in (‘clerk’,’steno’,’salesman’,’peon’); 7:- To display employee records whose name begin with character ‘S’ Select empno, ename, job, Sal from emp where ename like ‘S*’; 8:- To display employee records whose first and second are any characters and third must be h.remaining are any characters. Select empno, ename, job, sal, deptno from emp where ename like '??h*' 9:- To display employee records descending order by name. select * from emp order by ename desc; 10:- To display employee records ascending order by name. select * from emp order by ename asc;