| Database Systems Course Code: Maj/SE-206 | ||||
| Credit Hours: | 3(3+0) | Prerequisites: | Data Structures and Algorithms | |
| Course Learning Outcomes (CLOs): | ||||
| At the end of the course the students will be able to: | Domain | BT Level* | ||
| Explain fundamental database concepts. | C | 2 | ||
| Design conceptual, logical and physical database schemas using different data models. | C | 5 | ||
| Identify functional dependencies and resolve anomalies by normalizing database tables. | C | 2 | ||
| Use Structure Query Language (SQL) for database definition and manipulation in any DBMS. | C | 4 | ||
| BT= Bloom’s Taxonomy, C=Cognitive domain, P=Psychomotor domain, A=Affective domain | ||||
| Course Contents: (Theory) |
| Part One Overview: Chapter 1: Database Environment and Development Process: Course Introduction, General concept (Data, information, Meta data), File based Systems, Disadvantages of File based system, Database, DBMS Chapter 1: Database Environment and Development Process: Advantages and Disadvantages of DBMS, Database applications, Components of DBMS, History of DBMS, Range of DBMS, DBMS architecture, 3-Tiers Architecture, Data IndependenceDatabase Analysis: Modeling Data in Organization: Chapter 2 ER Model: Basic Modeling Concepts, ER model construct & notations, Entities (entity type, entity instance, strong Vs weak entity) Chapter 2 ER Model: Attributes (required Vs Optional attribute, simple Vs composite attribute, single-valued Vs multi-valued attributes, stored Vs Derived attribute, identifier attribute), Relationship(Degree of a relationship (unary, binary, ternary, quaternary), 1-1, 1-M, M-M relationship, optional & mandatory relationship, associative entity )Chapter 3: Enhanced ER Model : The Enhanced ER model , Representing Super type & Sub type, Attribute inheritance, Specialization & Generalization, Specifying completeness constraints ( Total Specialization rule, Partial Specialization rule) Chapter3: EER Model Problems and Case Studies Specifying Disjointness constraints ( Disjoint, Overlap) , Defining Subtype Discriminator, Super type / Subtype Hierarchy, Entity Clustering, EER- Diagram Problems & Case studies.Chapter 4: Logical Database Design & Relational Model: Transforming EER Diagram into Relations: Map regular entities, Map weak entities, Map associative entities, Map unary relation (Unary one-to-one, Unary many-to-many), Map binary relation, Map ternary relation, Map Super type/ Subtype relationship, Merging Relations. Chapter 4: Relational Model & Normalization: Relational Data model, Properties of Relation, Relational Keys (Primary key, Composite Primary key, Foreign key) , Integrity Constraints ( Domain Constraints, Entity Integrity, Referential Integrity, Action Assertion, Normalization (1NF, 2NF, 3NF, BCNF), Practice Exercises (Normalization). Producing Readable output with SQL Plus, DML ( Insert, Update, Delete), DDL ( Create, Alter, Drop).Chapter 5: Physical Database Design and Performance (Designing Fields, Designing Physical Records & Denormalization, Designing Physical Files ( File Organization: Sequential, Indexed, Hashed), Optimizing for Query Performance)Chapter 6: Introduction to SQL: Structured Query Language , Select Statement: selecting all columns, selecting specific columns, column heading default, defining column aliases, arithmetic expressions, using arithmetic operators, operator precedence, defining a NULL value, concatenation operators, duplicate rows, eliminating duplicate rows, limiting rows using Where clause, Using BETWEEN, IN, LIKE, IS NULL operators, using AND, OR, NOT operator.Chapter 6: Introduction to SQL sorting data (ORDER BY clause), sorting by multiple columns, Single-Row function( character function, case-conversion function), NVL function, displaying data from multiple table, Joins ( equijoin, inner join, outer join, self-join), Aggregate data using group Functions, Sub queriesChapter 7: Advanced SQL Create Views, Sequence, Index, Synonyms, Controlling user access ( create user, create role, WITH GRANT OPTION, PUBLIC, Revoke)PL/SQL block structure, Types of variable, Variable declaration in PL/SQL, Variable initialization, %TYPE attribute, PL/SQL block syntax, SQL statement in PL/SQL ( Select statement in Pl/SQL, Retrieving data, DML operations).SQL cursor, Control Structures in PL/SQL, Iterative Control LOOP statement, Writing Explicit CursorsOverview of Database Backup & Recovery TechniqueOverview of Database Security & Authentication |
| Teaching Methodology |
| Lectures, Written Assignments, Practical labs, Semester Project, Presentations |
| Text Book |
| Modern Database Management. By Fred, R. McFadden Jeffrey, A Hoffer, Mary, B. Prescott. 2005.Introduction to Oracle: SQL and PL/SQL |
| Reference Materials |
Database Systems: A Practical Approach to Design, Implementation & Management by Thomas Connnolly, Carolyn BeggFundamentals of Database Systems, 5/E, Elmasri and Navathe, Addison-Wesley, ISBN: 0-201-74153-9 |
| Database Systems (Practical)Course Code: Maj/SE-206-P | |||
| Credit Hours: | 1(0+1) | Prerequisites: | Data Structures and Algorithms |
| Learning Objectives | |||
| Lab sessions cover the practical implementation of the course. To create and analyse a database, to and produce a viable model and implementation of a database to meet requirements. | |||
| Learning Outcomes | |||
| Upon successful completion of this course, students will have the skills to analyze business requirements and produce a viable model and implementation of a database to meet requirements. | |||
| Course Contents: (LAB) | |||
| Introduction to Access to brief the database objects ( tables, query, forms, report)tables, query, forms, reportIntroduction to Modeling toolUsage of Modeling toolUsage of Modeling toolStructured Query Language Select Statement: selecting all columns, selecting specific columns, column heading default, defining column aliases, arithmetic expressions using arithmetic operators, operator precedence, defining a NULL value, concatenation operators, duplicate rows, eliminating duplicate rows,Where clause, Using BETWEEN, IN, LIKE, IS NULL operators, using AND, OR, NOT operator, sorting data (ORDER BY clause), sorting by multiple columnsSingle-Row function( character function, case-conversion function), NVL function, displaying data from multiple table, Joins ( equijoin, inner join, outer join, self-join), Aggregate data using group Functions, Sub queriesProducing Readable output with SQL Plus: DML ( Insert, Update, Delete), DDL ( Create, Alter, Drop)Create Views, Sequence, Index, Synonyms, Controlling user access( create user, create role, WITH GRANT OPTION, PUBLIC, Revoke)PL/SQL block structure, Types of variable, Variable declaration in PL/SQL, Variable initialization, %TYPE attribute, PL/SQL block syntax, SQL statement in PL/SQL ( Select statement in Pl/SQL, Retrieving data, DML operations)SQL cursor, Control Structures in PL/SQL, Iterative Control LOOP statement, Writing Explicit Cursors | |||
| Teaching Methodology | |||
| Lectures, Written Assignments, Practical labs, Semester Project, Presentations | |||
| Text Book | |||
| Modern Database Management. By Fred, R. McFadden Jeffrey, A Hoffer, Mary, B. Prescott. 2005.Introduction to Oracle: SQL and PL/SQL | |||
| Reference Materials | |||
Database Systems: A Practical Approach to Design, Implementation & Management by Thomas Connnolly, Carolyn BeggFundamentals of Database Systems, 5/E, Elmasri and Navathe, Addison-Wesley, ISBN: 0-201-74153-9 | |||