Course Outline Course Outline T
Tiittllee DDaattaabbaasse e SSyysstteemmss C Cooddee CCMMPP--337700--33 Credit Hours Credit Hours 3+13+1 Theory/week: Theory/week: Weight Weight 3 3 Cr. Cr. Hrs.Hrs. Contact
Contact Hours Hours 3 3 Hrs.Hrs.
Lectures: 2 Lectures: 2 Duration Duration 1.5 1.5 Hrs.Hrs. Lab/week: Lab/week: Weight Weight 1 1 Cr. Cr. Hrs.Hrs. Contact
Contact Hours Hours 3 3 Hrs.Hrs. Labs.
Labs.
Duration Hrs.
Duration Hrs.
Prerequisite
Prerequisite None None
Prerequisite Prerequisite Skill/Knowledge/Un Skill/Knowledge/Un derstanding derstanding •
• Strong grip on business processesStrong grip on business processes
•
• FamiliarizatiFamiliarization and practical on and practical experience of relation algebra experience of relation algebra and set operations.and set operations.
Required Study Hours Required Study Hours
Follow Up Follow Up Program Name
Program Name BS-CSBS-CS
Category
Category CoreCore
Aims and Objectives
Aims and Objectives •• To understand To understand the basic the basic concepts of concepts of DatabaseDatabase •
• To discuss the advantages of database system over conventional file systemTo discuss the advantages of database system over conventional file system •
• To make a logical and analytical Comparison of Different Data ModelsTo make a logical and analytical Comparison of Different Data Models •
• To provide strong dimensions, strengths and future prospects of Database Systems.To provide strong dimensions, strengths and future prospects of Database Systems. •
• To design and implementation of Database ModelingTo design and implementation of Database Modeling •
• To Transform ERD (Entity Relationship Diagram) into relationsTo Transform ERD (Entity Relationship Diagram) into relations •
• To develop Good Skills in SQL (Structured Query Language)To develop Good Skills in SQL (Structured Query Language) •
• To discuss Advantages of Distributed database over Centralized DatabaseTo discuss Advantages of Distributed database over Centralized Database •
Learning Outcomes • Students will be able to understand the Database System environment
• Students will be able to Design and Implement a Relational database for real life problems • Students will be Expertise in writing SQL queries
• Students will have Good concepts of modeling techniques (ERD)
• Students will be able to suggest a Centralized Distributed system according to organizational needs • Students will be able to design and implement solutions for the small business organizations
Syllabus Topics: File Systems and Databases: Introduction, A File system Critique, Database Systems, Database Models. Introduction to RDBMS: Logical view of Data; Entities and Attributes, Tables and their Characteristics, Keys; Integrity Rules. Relational Algebra: Relational Database Operators, System Catalog. Entity Relationship (E-R) Modeling: Basic Modeling Concepts, Data Models, The Entity Relationship (E-R) Model. Normalization of Database Tables: Objectives, Forms, Normalization and Database Design, Denormalization, Structured Query Language (SQL): Introduction, DDL Commands, DML Commands, DCL Commands, Complex Queries and SQL Functions, Procedural SQL; Triggers, Stored procedures. Database Design: The System Development Life Cycle (SDLC), The Database Life Cycle (DBLC), Database Design Strategies, Transaction Management and Concurrency Control: Introduction, Transaction Properties and Types, Concurrency Control Issues, Database Recovery Management. DDBMS: Evolution, Components, Distributed processing and distributed databases, Distributed database transparency features. Distributed database design, Data fragmentation, Data replication, Data allocation, Client-server versus DDBMS, C.J. Date’s 12 commandments for distributed databases.
Text Book/s
A.
“Database Systems” Design, Implementation, Management by Peter Rob and Carlos Coronel, 5thEd., Course Technology, 2002. ISBN 0-619-06269-xReference Material 1. Introduction to PL\SQL by Oracle Press
2.
C. J. DATES “Database Management Systems”8thEd. 2001. ISBN 0-901-543432-8 3. M. TAIMER “Distributed Database Management Systems” 2nd Edition4. Fred R. McFadden, Jeffrey Hoffer, “Modern Database Management ” Design, Implementation, Management, 5th Edition, ISBN 0-201-47432-x
5. Dr. Nayyer Masood (VU), Database Systems-Video Lectures Instructional Aids/Resources
• Windows Environment
• Oracle 9i client & Server / SQL Server
• Erwin
• Multimedia in Class Rooms as well as in Labs • Photocopy Facility for Handouts/Case Studies
Assessment Criteria Sessional 25% Mid 35% Final 40% Total 100%
Assignment, Presentations and Test 20
Class Participation 05 Paper 35 Paper 40
Project (50 % of Lab Marks) Recommendations Project is the compulsory part of this course.
Framework
Week Lectu
re
Topic Source
(Book-Chapter No. Page No.)
Study Hours
Recommendations for Learning Activities
(Mention Assignments, Test, Quizzes, Practical, Case Study, Projects, Lab Work or Reading Assignments)
1
1File Systems and Databases: Introducing the Database; Why Database Design is important, A practical approach to Database Design. Historical roots of the database; Files and File system, A File system Critique; File System Data Management, Structural and Data dependence, Field Definitions and Naming Conventions, Data Redundancy.
Text A-Ch1-(5-16) 2
N.A.
2
Database Systems: Database system Environment, Types of Database Management Systems, DBMS Functions, Managing the Database System, Database Design and Modeling.
Text A-Ch1-(17-23) 2
Lab:
2
3Database Models: Hierarchical Model, Network Model, Relational Model, Entity Relationship Data Model, Object-Oriented Model, The Evolution of Data Models; Database Models and the Internet.
Text A-Ch1-(23-45) 2+2
Preparation of Next Class Test
4 Introduction to RDBMS: A logical view of Data; Entities
and Attributes, Tables and their Characteristics, Keys. Text A-Ch2-(57-62) 2+2 Test Lab:
3
5 Integrity Rules: Entity Integrity, Referential Integrity Text A-Ch2-(62-70) 2 Submission of one Page Proposal for Term Project 6 Relational Database Operators: Data Dictionary and
System Catalog. Text A-Ch2-(70-78) 2
Lab:
4
7Structured Query Language (SQL): Introduction to Data Definition Commands, Data Manipulation Commands, Data Control Language.
Text A-Ch5-(209-221) 2
8
SQL:Data Manipulation Commands, Data Entry, Saving the Table Contents, Listing the Table Contents, Making a Correction, Restoring the Table Contents, Deleting Table Rows. Group Functions.
Text A-Ch5-(222-226) 2 Lab: To Practice the SQL Data Definition Commands, Data Manipulation Commands
5
9 SQL: Using Joins
Sub Queries Text A-Ch5-(227-245) 2+ 1
Assignment
10
Entity Relationship (E-R) Modeling: Basic Modeling Concepts; Data Models; Degrees of Data Abstraction; Conceptual Model, Internal Model, External Model, Physical Model.
Text A-Ch3-(109-118) 2
Lab: To Practice the SQL Data Management Commands, Complex queries.
6
11 Entity Relationship (E-R) Model: Entities, Attributes, Relationships, Connectivity and Cardinality, Relationship Strength (Existence Dependency), Relationship Participation, Relationship Strength and weak entities, Relationship Degree.12
Entity Relationship (E-R) Model:Composite Entities, Entity Super types and subtypes, Comparison of E-R Modeling Symbols.
Text A-Ch3-(135-146) 2+3
Submission of Preliminary Report of Term Project Preparation for Next class Pre Mid Test
Lab:
7
13 Developing an E-R Diagram: Challenge of Database
Design, Conflicting Goals. Text A-Ch3-(146-157) 1+2
Pre Mid Test 14 Transform E-R Diagram into Database Structure:
General Rules Governing Relationships Among Tables. 2
Submission of E-R Model of Final Project Lab:
8
15
Normalization of Database Tables: Need for Normalization, Conversion to First Normal Form,
Conversion to Second Normal Form.
Text A-Ch4-(175-183) 1
N.A.
16 Normalization: Conversion to Third Normal Form,
Boyce-Codd Normal Form (BCNF). Text A-Ch4-(183-188) 1+4
Submission of Revised E-R Model of Final Project Lab:
MID-TERM
9
17 Normalization and Database Design: Higher Level
Normal Forms, Denormalization. Text A-Ch4-(188-196) 1+1
Assignment
18
Structured Query Language (SQL): Complex Queries and SQL Functions; Ordering a Listing, Listing Unique Values, Aggregate Function in SQL, Grouping Data
Text A-Ch5-(245-255) 1
Lab: To Practice, Complex Queries, SQL Function, and listing issues.
10
19Structured Query Language (SQL): Virtual Tables, Define Views, view Types, SQL Indexes, Joining Database Tables
Text A-Ch5-(256-263) 1+1
Transform the Term Project E-R Diagram into Database Structure
20
Procedural SQL: Triggers, Stored Procedures, PL/SQL Stored Functions.
Text A-Ch5-(263-278) 1+1
Assignment
Lab: To Practice, creating a view, Joining Database tables, Triggers, Stored Procedure
11
21Database Design: Changing Data into Information, Information System, System Development Life Cycle (SDLC); Planning, Analysis, Detailed Systems Design, Implementation, Maintenance
Text A –Ch6 (319-324) 2
22
Database Life Cycle (DBLC): Database Initial Study; Database Design, Implementation and Loading, Testing and Evolution. Database Design Strategies. Centralized versus Decentralized Design.
Text A –Ch6 (325-351)
1+2
Preparation for Next Class Quiz Lab:
12
23Transaction Management and Concurrency Control: What is a Transaction; Evaluating Transaction Results, Transaction Properties, Transaction Management with SQL, Transaction Log, Transaction Types.
Text A –Ch7 (459-463)
Ref. 3-Ch10 1+3
Quiz
24 Concurrency Control: Lost Updates, Un-committed Data, Inconsistent Retrievals, Dirty Data, Fuzzy Read, Scheduler.
Text A –Ch7 (464-468)
Ref. 3-Ch11 1
Final Project front end submission Lab:
13
25 Concurrency Control with locking Methods: Lock Granularity, Lock Types, Two-Phase Locking to Ensure Serializability, Deadlocks.Text A –Ch7 (468-475) Ref. 3-Ch11
26
Concurrency control with Time Stamping Methods: Concurrency control with optimistic Methods. Database Recovery Management; Transaction Recovery, Architectural Considerations, Recovery information.
Text A –Ch7 (475-478)
Ref. 3-Ch12 1+4
Preparation for Pre Final Test
14
27 Database Connectivity Issues. 2+6 Pre Final Test
Lab: To Practice the Open Database Connectivity Issues 28 DDBMS: Evolution, Advantages & Disadvantages.Distributed processing and distributed databases.
What is a DDBMS?, Components.
Text A –Ch10 (485-493)
Ref. 3-Ch1 1 Final Submission of the Final project with Documentation
15
29DDBMS: Levels of data processed distribution, Distributed database transparency features, Distribution transparency, Transaction transparency, Performance transparency and query optimization.
Text A –Ch10 (494-507)
Ref. 3-Ch1 1+6
30
DDBMS: Distributed database design; Data fragmentation, Horizontal fragmentation, Vertical fragmentation, Mixed fragmentation. Text A –Ch10 (508-510) Ref. 3-Ch5 2 Lab:
16
31DDBMS:Data replication, Data allocation, Client-server versus DDBMS. C.J. Date’s 12 commandments for distributed databases.
Text A –Ch10 (512-515)
Ref. 3-Ch5 1+5
Viva/Presentation
32 Discuss Future Trends.
Revision. 1+6
Revise the course for the preparation of Final Paper. Lab:
