BSc CS sem 1 Database Systems techmax/notes Download
Objectives: The objective of this course is to introduce the concept of the DBMS with respect to the relational model, to specify the functional and data requirements for a typical database application and to understand creation, manipulation and querying of data in databases Expected Learning Outcomes 1) Students should be able to evaluate business information problem and find the requirements of a problem in terms of data. 2) Students should be able to design the database schema with the use of appropriate data types for storage of data in database. 3) Students should be able to create, manipulate, query and back up the databases. Unit I Introduction to DBMS – Database, DBMS – Definition, Overview of DBMS, Advantages of DBMS, Levels of abstraction, Data independence, DBMS Architecture Data models - Client/Server Architecture, Object Based Logical Model, Record Based Logical Model ( relational, hierarchical, network) Entity Relationship Model - Entities, attributes, entity sets, relations, relationship sets, Additional constraints ( key constraints, participation constraints, weak entities, aggregation / generalization, Conceptual Design using ER ( entities VS attributes, Entity Vs relationship, binary Vs ternary, constraints beyond ER) Relational data model– Domains, attributes, Tuples and Relations, Relational Model Notation, Characteristics of Relations, Relational Constraints - primary key, referential integrity, unique constraint, Null constraint, Check constraint ER to Table- Entity to Table, Relationship to tables with and without key constraints. 15L Unit II Schema refinement and Normal forms: Functional dependencies, first, second, third, and BCNF normal forms based on primary keys, lossless join decomposition. Relational Algebra operations (selection, projection, set operations union, intersection, difference, cross product, Joins –conditional, equi join and natural joins, division) DDL Statements - Creating Databases, Using Databases, datatypes, Creating Tables (with integrity constraints – primary key, default, check, not null), Altering Tables, Renaming Tables, Dropping Tables, Truncating Tables, Backing Up and Restoring databases DML Statements – Viewing the structure of a table insert, update, delete, Select all columns, specific columns, unique records, conditional select, in clause, between clause, limit, aggregate functions (count, min, max, avg, sum), group by clause, having clause 15L 9 Unit III Functions – String Functions (concat, instr, left, right, mid, length, lcase/lower, ucase/upper, replace, strcmp, trim, ltrim, rtrim), Math Functions (abs, ceil, floor, mod, pow, sqrt, round, truncate) Date Functions (adddate, datediff, day, month, year, hour, min, sec, now, reverse) Joining Tables – inner join, outer join (left outer, right outer, full outer) Subqueries – subqueries with IN, EXISTS, subqueries restrictions, Nested subqueries, ANY/ALL clause, correlated subqueries Database Protection: Security Issues, Threats to Databases, Security Mechanisms, Role of DBA, Discretionary Access Control Views (creating, altering dropping, renaming and manipulating views) DCL Statements (creating/dropping users, privileges introduction, granting/revoking privileges, viewing privileges) 15L Text books: 1. Ramez Elmasri & Shamkant B.Navathe, Fundamentals of Database Systems, Pearson Education, Sixth Edition, 2010 2. Ramakrishnam, Gehrke, Database Management Systems, McGraw‐Hill, 2007 3. Joel Murach, Murach’s MySQL, Murach, 2012 Additional References: 1. Robert Sheldon, Geoff Moes, Begning MySQL, Wrox Press, 2005.