الدرس · مقدمة · IT1220

مقدمة الفصل

ماذا ستتعلم في هذا الفصل

Why databases are needed: what a DBMS contains and the drawbacks of storing data in file systems.
Levels of abstraction, schemas and instances, and physical data independence.
Data models, the relational model, DDL and DML (SQL).
Database design and its two approaches (the E-R model and normalization).
The database engine: storage manager, query processing and transaction manager; users, architectures and history.

أقسام الفصل

1Database management systemsنظم إدارة قواعد البيانات
2Drawbacks of using file systems to store dataعيوب تخزين البيانات في نظام الملفات
3Levels of abstractionمستويات التجريد
4Schemas, instances and physical data independenceالمخطط والنسخة واستقلال البيانات المادي
5Data models and the relational modelنماذج البيانات والنموذج العلائقي
6DDL, DML and SQLلغة التعريف ولغة المعالجة ولغة SQL
7Database design and design approachesتصميم قواعد البيانات وطرقه
8Object-relational data models and XMLالنماذج الكائنية العلائقية وXML
9The database engineمحرك قاعدة البيانات
10Users, architectures and historyالمستخدمون والبنى والتاريخ

تلميح: استخدم أسهم لوحة المفاتيح للتنقل بين الشرائح.

What a DBMS containsنظام إدارة قواعد البيانات

Database management systems

Database management system (DBMS)

Contains information about a particular enterprise: a collection of interrelated data, a set of programs to access the data, and an environment that is both convenient and efficient to use.

Database applications (slide 3)

Banking: transactions
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Online retailers: order tracking, customized recommendations
Manufacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax deductions

Databases can be very large.
Databases touch all aspects of our lives.

University application programs (slide 4)

Add new students, instructors, and courses.
Register students for courses, and generate class rosters.
Assign grades, compute GPA and generate transcripts.
In the early days, database applications were built directly on top of file systems.

Which of the following is NOT part of what a DBMS contains?

aA separate file format for each new applicationbA collection of interrelated datacA set of programs to access the datadAn environment that is both convenient and efficient to use

Drawbacks of file systemsعيوب نظام الملفات

Drawbacks of using file systems to store data

Drawbacks of using file systems to store data (slides 5–6)

Drawbackالعيب	What goes wrong (slides 5–6)ما الذي يحدث
Data redundancy and inconsistency	Multiple file formats, duplication of information in different files
Difficulty in accessing data	Need to write a new program to carry out each new task
Data isolation	Multiple files and formats
Integrity problems	Constraints (e.g., account balance > 0) "buried" in program code hard to add or change constraints
Atomicity of updates	Failures may leave partial updates — a funds transfer should complete or not happen at all
Concurrent access by multiple users	Uncontrolled concurrent access leads to inconsistencies — two people withdrawing 50 each from a balance of 100 at the same time
Security problems	Hard to provide user access to some, but not all, data

Database systems offer solutions to all the above problems.

Three levels of abstractionمستويات التجريد

An architecture for a database system (slide 8)

View levelمستوى العرض

view 1view 2…view n

Physical levelالمستوى المادي

The lowest level of abstraction: describes how a record (e.g., instructor) is stored.

Logical levelالمستوى المنطقي

Describes what data is stored in the database, and the relationships among the data.

View levelمستوى العرض

Application programs hide details of data types; views can also hide information (such as an employee's salary) for security purposes.

من الشرائح

type instructor = record — ID : string; name : string; dept_name : string; salary : integer; end;

Schemas and instancesالمخطط والنسخة

Schemas, instances and physical data independence

Schemaالمخطط

The overall design of the database.
Logical schema: the overall logical structure of the database at the logical level. Example: the database consists of information about a set of customers and accounts in a bank and the relationship between them.
Physical schema: the overall physical structure of the database at the physical level.
Analogous to the type information of a variable in a program.

Instanceالنسخة

The actual content of the database at a particular point in time.
Analogous to the value of a variable.

Physical data independence

The ability to modify the physical schema without changing the logical schema. Applications depend on the logical schema; interfaces between levels should be well defined so that changes in some parts do not seriously influence others.

Data modelsنماذج البيانات

Data model

A collection of tools for describing data, data relationships, data semantics and data constraints.

1Relational modelالنموذج العلائقيAll the data is stored in various tables (columns and rows).
2Entity-Relationship data modelنموذج الكيان والعلاقة**Mainly for database design.**
3Object-based data modelsالنماذج الكائنيةObject-oriented and object-relational.
4Semi-structured data modelالنموذج شبه المهيكلXML.
5Other older modelsنماذج أقدمNetwork model; hierarchical model.

A sample relational databaseقاعدة علائقية نموذجية

Data models and the relational model

Relational model: all the data is stored in various tables. The columns are the attributes; each row holds one record.

(a) The instructor table (slides 11, 12 and 19)

ID	name	dept_name	salary
22222	Einstein	Physics	95000
12121	Wu	Finance	90000
32343	El Said	History	60000
45565	Katz	Comp. Sci.	75000
98345	Kim	Elec. Eng.	80000
76766	Crick	Biology	72000
10101	Srinivasan	Comp. Sci.	65000
58583	Califieri	History	62000
83821	Brandt	Comp. Sci.	92000
15151	Mozart	Music	40000
33456	Gold	Physics	87000
76543	Singh	Finance	80000

(b) The department table (slides 12 and 19)

dept_name	building	budget
Comp. Sci.	Taylor	100000
Biology	Watson	90000
Elec. Eng.	Taylor	85000
Music	Packard	80000
Finance	Painter	120000
History	Painter	50000
Physics	Watson	70000

Data Definition Language (DDL)لغة تعريف البيانات

DDL, DML and SQL

DDL

A specification notation for defining the database schema.

من الشرائح

create table instructor (ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2))

The DDL compiler generates a set of table templates stored in a data dictionary.
The data dictionary contains metadata (data about data): the database schema; integrity constraints (e.g., primary key — ID uniquely identifies instructors); authorization (who can access what).

A data dictionary exampleمثال قاموس بيانات

DDL, DML and SQL

Slide 14 shows a data dictionary kept as a spreadsheet: for every table, each attribute with its contents, type, format, domain, whether it is required, its key role (PK/FK), the table an FK references, and comments. The six tables follow, cell by cell.

Table: Parent (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
ParentID	Parent ID	INT	123	0 - 4294967295	Y	PK	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_fname	Parent first name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_lname	Parent last nmae	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_street	Parent street address	VARCHAR(45)	123 Street	0 - 9, a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_city	Parent city	VARCHAR(30)	City	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_state	Parent state	CHAR(2)	AZ	A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_zip	Parent zip code	INT	1234567890	0 - 4294967295	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_phone	Parent phone number	BIGINT	1234567890	0 - 18446744073709551615	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_email	Parent email address	VARCHAR(100)	abc@abc.com	0 - 9, a - z, @, .	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Parent_notes	Parent notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	Carpool availability noted

Table: Player (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
PlayerID	Player ID	INT	123	0 - 4294967295	Y	PK	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
TeamID	Team ID	INT	123	0 - 4294967295	Y	FK	Team	لا تذكر الشرائح شيئًا هنا.
Player_fname	Player first name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Player_lname	Player last name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Player_age	Player age	TINYINT	12	0 - 255	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Player_phone	Player phone number	BIGINT	1234567890	0 - 18446744073709551615	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Player_email	Player email address	VARCHAR(100)	abc@abc.com	0 - 9, a - z, @, .	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Player_notes	Player notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	Carpool needs noted

Table: Registers (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
ParentID	Parent ID	INT	123	0 - 4294967295	Y	PK/FK	Parent	لا تذكر الشرائح شيئًا هنا.
PlayerID	Player ID	INT	123	0 - 4294967295	Y	PK/FK	Player	لا تذكر الشرائح شيئًا هنا.
Reg_cost	Registration cost	DECIMAL(5,2)	123.45	0 - 9	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Reg_date	Registration date	DATE	YYYY-MM-DD	0 - 9	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Reg_notes	Registration notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	Discounted prices explained

Table: Team (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
TeamID	Team ID	INT	123	0 - 4294967295	Y	PK	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Team_name	Team name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Team_notes	Team notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.

Table: Coach (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
CoachID	Coach ID	INT	123	0 - 4294967295	Y	PK	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
TeamID	Team ID	INT	123	0 - 4294967295	Y	FK	Team	لا تذكر الشرائح شيئًا هنا.
Coach_fname	Coach first name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Coach_lname	Coach last name	VARCHAR(45)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Coach_phone	Coach phone number	BIGINT	1234567890	0 - 18446744073709551615	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Coach_notes	Coach notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	Coaching role noted

Table: Color (slide 14)

Attribute Name	Contents	Type	Format	Domain	Required	PK/FK	FK referenced table	Comments
TeamID	Team ID	INT	123	0 - 4294967295	Y	PK/FK	Team	لا تذكر الشرائح شيئًا هنا.
ColorID	Color ID	INT	123	0 - 4294967295	Y	PK	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Color_name	Color name	VARCHAR(25)	Abc	a - z, A - Z	Y	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.
Color_notes	Color notes	VARCHAR(255)	لا تذكر الشرائح شيئًا هنا.	0 - 9, a - z, A - Z, all symbols	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.	لا تذكر الشرائح شيئًا هنا.

DML and SQLلغة المعالجة وSQL

DML (query language)

A language for accessing and manipulating the data organized by the appropriate data model.

Pure languagesلغات نقية

Used for proving properties about computational power and for optimization.
Relational algebra; tuple relational calculus; domain relational calculus.

Commercial languagesلغات تجارية

Used in commercial systems.
SQL is the most widely used commercial language.

SQL is NOT a Turing machine equivalent language: to compute complex functions it is usually embedded in a higher-level language.
Application programs access databases through language extensions to allow embedded SQL or an application program interface (e.g., ODBC/JDBC) that sends SQL queries to a database.

Database designتصميم القاعدة

Logical designالتصميم المنطقي

Deciding on the database schema — finding a "good" collection of relation schemas.
Business decision: what attributes should we record?
Computer science decision: what relation schemas, and how are the attributes distributed among them?

Physical designالتصميم المادي

Deciding on the physical layout of the database.

Slide 18 asks: "Is there any problem with this relation?"

ID	name	salary	dept_name	building	budget
22222	Einstein	95000	Physics	Watson	70000
12121	Wu	90000	Finance	Painter	120000
32343	El Said	60000	History	Painter	50000
45565	Katz	75000	Comp. Sci.	Taylor	100000
98345	Kim	80000	Elec. Eng.	Taylor	85000
76766	Crick	72000	Biology	Watson	90000
10101	Srinivasan	65000	Comp. Sci.	Taylor	100000
58583	Califieri	62000	History	Painter	50000
83821	Brandt	92000	Comp. Sci	Taylor	100000
15151	Mozart	40000	Music	Packard	80000
33456	Gold	87000	Physics	Watson	70000
76543	Singh	80000	Finance	Painter	120000

Two design approachesطريقتا التصميم

Database design and design approaches

A methodology is needed to ensure that each of the relations in the database is "good". Two ways of doing so:

Entity Relationship Modelنموذج الكيان والعلاقة

Models an enterprise as a collection of entities and relationships; represented diagrammatically by an entity-relationship diagram.

Normalization Theoryنظرية التطبيع

Formalize what designs are bad, and test for them.

Object-relational models and XMLالكائني العلائقي وXML

Object-relational data models and XML

Object-relational data models

Relational model: flat, "atomic" values.
Extend the relational model by including object orientation and constructs for added data types.
Allow attributes of tuples to have complex types, including non-atomic values such as nested relations.
Preserve relational foundations (declarative access to data) while extending modeling power; upward compatibility with existing relational languages.

XML: Extensible Markup Language

Defined by the WWW Consortium (W3C).
Originally intended as a document markup language, not a database language.
The ability to specify new tags, and to create nested tag structures, made XML a great way to exchange data, not just documents.
XML has become the basis for all new generation data interchange formats.
A wide variety of tools is available for parsing, browsing and querying XML documents/data.

Storage manager and query processingالتخزين والاستعلام

The database engine

Storage manager

A program module that provides the interface between the low-level data stored in the database and the application programs and queries submitted to the system. Tasks: interaction with the OS file manager; efficient storing, retrieving and updating of data. Issues: storage access, file organization, indexing and hashing.

Query processing (slide 25)

1
Parsing and translation: the parser and translator turns the query into a relational-algebra expression.
2
Optimization: the optimizer, using statistics about data, produces an execution plan.
3
Evaluation: the evaluation engine runs the execution plan on the data and returns the query output.

A query can be evaluated in alternative ways: equivalent expressions, different algorithms for each operation.
The cost difference between a good and a bad way of evaluating a query can be enormous.
Need to estimate the cost of operations: it depends critically on statistical information about relations that the database must maintain.
Need to estimate statistics for intermediate results to compute the cost of complex expressions.

Transaction managementإدارة المعاملات

The database engine

Transaction

A collection of operations that performs a single logical function in a database application.

Transaction-management componentمكوّن إدارة المعاملات

Ensures the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures.

Concurrency-control managerمدير التحكم في التزامن

Controls the interaction among the concurrent transactions, to ensure the consistency of the database.

Database users and architecturesالمستخدمون والبنى

Users, architectures and history

Database users and administrators (slide 28)

Userالمستخدم	Uses / doesما يستعمله
Naive users (tellers, agents, web users)	Use application interfaces
Application programmers	Write application programs
Sophisticated users (analysts)	Use query tools
Database administrators	Use administration tools

Database system internals (slide 29)

Query processor
- DML queries
- DDL interpreter
- Compiler and linker
- Application program object code
- DML compiler and organizer
- Query evaluation engine
Storage manager
- Buffer manager
- File manager
- Authorization and integrity manager
- Transaction manager
Disk storage
- Data
- Indices
- Data dictionary
- Statistical data

Database architectures (slide 30)

Centralized
Client-server
Parallel (multi-processor)
Distributed

Figure 1.6 (slide 31)

Two-tier architectureثنائية الطبقات

Client: user → application.
Network.
Server: database system.

Three-tier architectureثلاثية الطبقات

Client: user → application client.
Network.
Server: application server → database system.

History of database systemsتاريخ قواعد البيانات