Introduction to Database Management Systems: Lecture Notes, Slides of Database Programming

Download Introduction to Database Management Systems: Lecture Notes and more Slides Database Programming in PDF only on Docsity!

Database Systems

Lecture

Topic: Introduction to Database Management Systems

Instructor: Lecturer Ayesha Naseer

Course Learning Objectives

Course Contents Weekly Breakdown of the Topics Week/ Lec Topic CLOs PLOs Learning Domain Level of Learning

1. Introduction to Database Management CLO1 1 Cognitive 2
2. Introduction to Database Development CLO1 1 Cognitive 2
3. Conceptual Database Design (ERDs) CLO2 3 Cognitive 5
4. Developing Data Models for Business Databases CLO2 3 Cognitive 5
5. Converting ERDs to Relational Tables CLO2 3 Cognitive 5
6. Query Formulation with SQL CLO4 5 OHT I
7. Functional Dependencies CLO3 4 Cognitive 4
8. Normalization CLO3 4 Cognitive 4
9. Introduction to SQL Joins CLO4 5 Cognitive 5 Types of Joins ( Inner, Outer, Self) CLO4 5 Cognitive 5
10. Nested Queries with Joins CLO4 5 Cognitive 5
11. Introduction to T-SQL CLO4 5 Cognitive 5 (^12) OHT II 13 SQL Stored Procedures CLO 4 5 Cognitive 5
12. SQL Triggers CLO4 5 Cognitive 5 15, 16. Transaction Management CLO4 5 Cognitive 5
13. Applications: Case Study of database management CLO1,2,3, 4 1,3,4,5 Cognitive 5 (^18) Final Exam

Assessments  3 x Quizzes 10%  3 x Assignments 10%  Mid Term Exam 30%  Final Exam 50%  Lab Project 30%

Learning Objectives  Describe the characteristics of business databases and the features of database management system  Understand the importance of nonprocedural access for software productivity  Advances in database technology and contribution of database technology to modern society  Understand the impact of database management system architectures on distributed processing and software maintenance  Perceive career opportunities related to database application development and database administration

Database Characteristics  Database is a collection of persistent data that can be shared and interrelated.  Persistent means that data reside on stable storage such as a magnetic disk. For example, organizations need to retain data about customers, suppliers, and inventory on stable storage because these data are repetitively used. Persistency depends on relevance of intended usage.  Shared means that a database can have multiple uses and users. A database provides a common memory for multiple functions in an organization. For example, a personnel database can support payroll calculations, performance evaluations, government reporting requirements, and so on. Many users can access a database at the same time. For example, many customers can simultaneously make airline reservations.  Interrelated means that data stored as separate units can be connected to provide a whole picture. For example, a customer database relates customer data (name, a d d r e s s ,... ) to order data (order number, order d a t e ,... ) to facilitate order processing.

Simple Database Example to Depict Database Characteristics

Features of Database Management Systems A database management system (DBMS) is a collection of components that supports the creation, use, and maintenance of databases.

1. Database Definition To define a database, the entities and relationships must be specified. In most commercial DBMSs, tables store collections of entities. A table (Figure 1. 4 ) has a heading row (first row) showing the column names and a body (other rows) showing the contents of the table. Relationships indicate connections among tables. For example, the relationship connecting the student table to the enrollment table shows the course offerings taken by each student.

Features of Database Management Systems

3. Procedural Language Interface  Procedural Language Interface is a method to combine a nonprocedural language such as SQL with a programming language.  DBMSs provide the full capabilities of a programming language. For example, Visual Basic for Applications (VBA) is a programming language that is integrated with Microsoft Access. VBA allows full customization of database access, form processing, and report generation.  Most commercial DBMSs have a procedural language interface comparable to VBA. For example, Oracle has the language PL/SQL and Microsoft SQL Server has the language Transact-SQL.

Features of Database Management Systems

4. Transaction processing  Transaction processing enables a DBMS to process large volumes of repetitive work.  A transaction is a unit of work that should be processed reliably without interference from other users and without loss of data due to failures. Examples of transactions are withdrawing cash at an ATM, making an airline reservation, and registering for a course.  A DBMS ensures that transactions are free of interference from other users, parts of a transaction are not lost due to a failure, and transactions do not make the database inconsistent.

Summary of Features of DBMSs

Evolution of Database Technology

Architectures of DBMSs  In order to develop conceptual understanding about internal organization of DBMSs, this section describes two architectures or organizing frameworks.  These architectures promote a conceptual understanding rather than indicate how an actual DBMS is organized.

1. Data Independence and the Three Schema Architecture
2. Distributed Processing and the Client-Server Architecture

Data Independence and the Three Schema Architecture

The concept of data independence emerged to alleviate problems with program maintenance. Data independence means that a database should have an identity separate from the applications (computer programs, forms, and reports) that use it. The separate identity allows the database definition to be changed without affecting related applications. In the mid- 1970 s, the concept of data independence led to the proposal of the Three Schema Architecture. The word schema as applied to databases means database description.