Database Fundamentals

Introduction
Limitations of file-based system:

• Separation & Isolation of data

• Duplication of data
• Program data dependence
• Incompatible file formats

What is a database?

It’s a collection of related data.

What is database management system (DBMS)?

A software package/system to facilitate the creation and maintenance of a computerized database.

What is a database system?

It is the DBMS software together with the data itself. Sometimes, the applications are also included. (Software +
Database)

Difference between File System and DBMS:

Basis File System DBMS

The file system is software that manages
Structure and organizes the files in a storage medium DBMS is software for managing the database.
within a computer.
Redundant data can be present in a file
Data Redundancy In DBMS there is no redundant data.
system.
It doesn’t provide backup and recovery of It provides backup and recovery of data even if
Backup and Recovery
data if it is lost. it is lost.
There is no efficient query processing in the
Query processing Efficient query processing is there in DBMS.
file system.
There is less data consistency in the file There is more data consistency because of the
Consistency
system. process of normalization.
It has more complexity in handling as
Complexity It is less complex compared to DBMS.
compared to the file system.
File systems provide less security in DBMS has more security mechanisms as
Security Constraints
comparison to DBMS. compared to file systems.
It has a comparatively higher cost than a file
Cost It is less expensive than DBMS.
system.
Data Independence There is no data independence. In DBMS data independence exists.
User Access Only one user can access data at a time. Multiple users can access data at a time.
The user has to write procedures for
Meaning The user is not required to write procedures.
managing databases
Data is distributed in many files. So, it is not
Sharing Due to centralized nature sharing is easy
easy to share data
It gives details of storage and
Data Abstraction It hides the internal details of Database
representation of data
Integrity Constraints are difficult to
Integrity Constraints Integrity constraints are easy to implement
implement
Example Word Documents, Excel Sheets Oracle, SQL Server
Main components of database system:

What is metadata:

Advantages of Database system:

• Controlling redundancy
• Restricting unauthorized access
• Sharing data
• Enforcing integrity constraints
‫ى‬
‫ (زى أن‬business rules ‫) وكمان‬text ‫ (زى أن مفيش خانة المفروض يتحط فيها ارقام ويتحط فيها‬logic rules ‫ببق متأكد ان الداتا بتخضع لـ‬
‫ن‬
)id ‫موظفي لهم نفس الـ‬ ‫مفيش ن‬
‫اتني‬
• Inconsistency)‫ (تضارب‬can be avoided
‫يعن لو حصل ابديت لداتا هتسمع ن يف كل مكان مش هتحصل عند حد وحد ل‬ ‫ن‬
• Providing backup and recovery

Disadvantages of Database system:

• Needs expertise to use.
• DBMS is expensive (software and infrastructure).
• May be incompatible with any other available DBMS (can be avoided by using third party tool).
Cycle of having a database:
Step 1: Analysis and requirements gathering.

Achieved by System Analyst who is responsible for:

• Business analysis and requirements gathering.

Step 2: Database design

Achieved by Database Designer who is responsible for:

• Create database design (Conceptual Schema).

Step 3: Implementation

Achieved by Database Administrator (DBA) who is responsible for:

• Install DBMS.
• Create DB schema and populate data.
• Create users and authorize access to DB.
• Maintain DB performance.

Step 4: Application development

Achieved by Application Programmer who is responsible for:

• Develop, test & debug the application.

DBMS Architecture:

• External Schema: they are concerned with what data the user will see and how the data will be presented to
the user (Could be more than one like financial schema, HR schema, etc).
• Conceptual Schema (Logical model): they are concerned with what is represented (define the database
structures such as table sand constraints).
• Physical Schema (Physical Model): they are concerned with how the data are represented in the database,
how the data structures are implemented.

The architecture is divided into schemas to achieve data independence (a change in any schema doesn’t affect the
other schemas).

Data Models:

Mappings:
It’s the process of transforming requests and results between levels (schemas).

DBMS other functionalities:

• Text/Number/Audio/Video/Image
• Spatial data
• Time series data
• Data Mining (very important)
‫‪DBMS Environments:‬‬
‫‪Centralized environment:‬‬

‫‪ -‬هنا بيكون عندى ‪ mainframe‬واحد ومتوصل عليه مجرد ‪ monitors‬ن‬

‫يعن الـ ‪ database‬و الـ ‪ application‬عىل جهاز واحد وكل األجهزة متوصله‬
‫بيه عن طريق ‪ dummy terminals‬ببعت عليهم مجرد ‪ requests‬ن‬
‫يعن كل الـ ‪ processing‬عىل الـ ‪.mainframe‬‬

‫‪ -‬بيكون مكون من ‪ 2 tiers‬وهنا الـ ‪ application‬بيكون موجود عىل الـ ‪ thick client‬وهنا انا خفيت الـ ‪ load‬بتاع الـ ‪ processing‬لكن ىبق عندى‬
‫هبق مضطر اعمله عىل كل ‪ ، client‬وطبعا لسه عندى ‪ Single Point of Failure‬عند‬ ‫مشكلة أن لو جيت اعمل ابديت مثال للـ ‪ application‬ى‬
‫الـ‪.database server‬‬

‫‪ -‬هنا الـ ‪ client‬بيكون ‪ thin‬عشان أنا ن ن‬

‫بنل عليه ‪ app‬صغن زى ‪ applet‬أو ممكن يدخل من الـ ‪.browser‬‬
‫‪ -‬كدة انا حليت شوية مشكلة الـ‪ maintenance‬والـ‪ update‬ن‬
‫ألن ساعتها هعملها بس عند الـ‪ application server‬مش عند الـ‪.Thin Clients‬‬

‫‪ -‬مازال عندى هنا مشكلة ‪ point of failure‬عىل الـ‪ application tier‬وممكن أحلها عن طريق )‪:(n-tier architecture‬‬

‫متوزعي عليهم بحيث لو واحد وقع يكون نف بدايل او ى‬

‫حن‬ ‫ن‬ ‫‪ -‬وهنا بيكون عندى خيارين اما ان كل الـ ‪ applications‬يكونوا واحد وهنا كل الـ‪clients‬‬
‫ي‬
‫اقسم الـ‪ clients‬عليهم عشان اخف الـ‪.load‬‬
‫التان نان اعمل ‪ different applications‬مثال زى )‪ (HR app – financial app‬ويكون كل واحد متوصل عليه الـ‪ clients‬بتوعه‪.‬‬
‫‪ -‬الخيار ن‬
‫‪Distributed Environment:‬‬

‫‪ -‬الفكرة الرئيسية هنا نان بحل مشكلة الـ‪ Single Point of Failure‬عند الـ‪.Database‬‬
‫‪ -‬أول ن‬
‫منة هنا انها ‪.support high availability of database‬‬
‫ن‬
‫متوصلي ببعض بحاجة زى الـ‪ heart beats‬كدة أول ما واحد‬ ‫ن‬
‫والتني‬ ‫‪ -‬ن يف حالة الـ ‪ full replication‬بيكون عندى نسخة من الـ‪database server‬‬
‫التان بيشتغل وكل الـ ‪ requests‬بيحصلها ‪ rerouting‬عىل ن‬
‫التان‪.‬‬ ‫يقع ن‬

‫‪ -‬نف حالة الـ ‪ partial replication‬بعمل كون لجزء بس من الداتا وبشتغل عليه وكل ى‬
‫فنة باخد الـ‪ changes‬الىل عملتها عىل الـ‪ replica‬وبنقلها‬ ‫ى‬ ‫ي‬
‫للـ‪ headquarter‬عشان يفضل ‪.up-to-date‬‬
‫ن‬
‫التني‪.‬‬ ‫‪ -‬نف حالة الـ‪ fragmentation‬باخد الـ‪ fragments‬أما ‪ horizontal‬او ‪ vertical‬او ‪ hybrid‬ن‬
‫بي‬ ‫ي‬
Relational Database:
• It consists of relations or tables of relations.
• Each table consists of columns and tuples (records).
• The intersection between them is domain.
• The domain can only have a single value.
• Each table has a primary key that can be one column or multiple columns together.
• The primary key (underlined): - must contain a unique value for each row of data.
- cannot contain null values.

Entity Relationship Diagram (ERD):

• An Entity-Relationship Diagram (ERD) is a visual representation of the structure of a database.
• It’s a way to create conceptual/logical schema/design.

Entity

• An entity represents a real-world object or concept in the database.

• It can be something tangible (like a "Person" or "Car") or abstract (like an "Order" or "Course").
• In an ERD, entities are typically represented by rectangles.
• Example: In a university database, entities could be "Student," "Course," and "Professor."

Attribute

• Attributes are the properties or characteristics of an entity.

• Each attribute holds a specific piece of data about the entity.
• In an ERD, attributes are usually represented by ovals connected to their respective entities.
• Example: For the "Student" entity, attributes might include "Student ID," "Name," "Date of Birth," and
"Email."
 Relationship

• A relationship describes how two or more entities are related to each other.
• Relationships represent the associations between entities in the database.
• In an ERD, relationships are depicted by diamonds connecting entities.
• Example: In a university database, a relationship could be "Enrolls," which links "Student" and "Course,"
indicating that students enroll in courses.

How to construct an ERD?

Types of Attributes:

- Simple attributes: are not divisible and have a single value for a particular entity instance.

- Multi-valued attributes: have a set of values for the same entity instance.

- Composite attributes: can be divided int subparts.

- Derived attributes: can be calculated from another attribute or entity.

- Candidate (Primary) key: an attribute that can uniquely identify one specific occurrence of an entity. (Underlined
attribute)

Types of Entities
- Strong entity:

• Definition: A strong entity exists independently of other entities and has a primary key that uniquely
identifies each instance of the entity.
• Example: In a university database, "Student" and "Course" are strong entities because each student and
course can be uniquely identified by their "Student ID" and "Course ID," respectively.

- Weak entity:

• Definition:
o A weak entity cannot exist without being associated with another entity, known as its "owner" or
"parent" entity.
o It does not have a primary key of its own but relies on a "foreign key" or "partial key" from the
owner entity.
• Example: "Relative" in a company’s medical insurance system might be a weak entity if it is defined only in
the context of an "Employee". The "Employee ID" might be used as part of the identifying key for "Relative".
Relationships:

Relationship Degree

it’s the number of entities in a relationship.

Unary Relationship (Degree 1)

• Definition: A unary relationship involves only one entity type, where the entity is related to itself.
• Example: An "Employee" manages another "Employee". In this case, the relationship "Manages" is unary
because it involves the "Employee" entity both as the manager and the subordinate.

Binary Relationship (Degree 2)

• Definition: A binary relationship involves two different entity types. It is the most common type of
relationship in ERDs.
• Example: A "Student" enrolls in a "Course". The relationship "Enrolls" is binary because it involves two
entities: "Student" and "Course".

Ternary Relationship (Degree 3)

• Definition: A ternary relationship involves three different entity types. It represents a relationship that
includes three entities simultaneously.
• Example: A "Doctor" prescribes a "Medication" to a "Patient". The relationship "Prescribes" is ternary
because it involves the entities "Doctor," "Medication," and "Patient".

N-ary Relationship (Degree N)

• Definition: An N-ary relationship involves N different entity types, where N is greater than three. This is a
more complex relationship that includes multiple entities.
• Example: In a supply chain system, a relationship might involve "Supplier," "Product," "Warehouse," and
"Transporter," forming a quaternary (4-ary) relationship.

Summary of Relationship Degrees:

• Unary: Involves 1 entity (e.g., Employee manages Employee).

• Binary: Involves 2 entities (e.g., Student enrolls in Course).
• Ternary: Involves 3 entities (e.g., Doctor prescribes Medication to Patient).
• N-ary: Involves N entities (e.g., Supplier provides Product to Warehouse through Transporter).
 ‫‪Cardinality Ratio‬‬

‫•‬ ‫‪The cardinality ratio describes the numerical relationship between entities participating in a relationship.‬‬
‫•‬ ‫‪It defines how many instances of one entity can or must be associated with instances of another entity.‬‬

‫نفس سؤال ن يف كل اتجاه وبكتب الرقم عند الطرف الىل‬

‫ي‬ ‫هنا أنا بمسك كل ‪ relationship‬وبحدد الـ ‪ max number‬بتاع كل طرف عن طريق نان بسأل‬
‫بينتىه عنده السؤال‪.‬‬
‫ن‬
‫موظفي كتن ‪ ،‬فهكتب‬ ‫يعن مثال الموظف بيشتغل نف كام قسم ‪ ،‬قسم واحد ‪ ،‬ى‬
‫يبق هكتب واحد عند القسم ‪ ،‬طب القسم بيشتغل فيه كام موظف ‪،‬‬ ‫ن‬
‫ي‬
‫كتن عند الموظف‪.‬‬
‫ن‬
‫التني يكونوا ‪ ، M‬اما مثال لو‬ ‫‪ -‬ملحوظة مهمة‪ :‬لزم الـ ‪ cardinality‬نف حالة الـ ‪ ternary relationship‬تتطابق عىل نفس الـ ‪ ، side‬ن‬
‫يعن مثال‬ ‫ي‬
‫واحدة ‪ 1‬و واحدة ‪ M‬ساعتها لزم افك الـ ‪ ternary‬واخليها ‪ closed loop‬من الـ ‪.binary‬‬
Participation

• Participation refers to the extent to which entities in a relationship must participate in that relationship.
• It indicates whether the presence of a relationship between entities is mandatory (must) or optional (may).
o Total Participation: Every instance of the entity is involved in the relationship (Mandatory
participation). Represented by a double line in ERDs.
o Partial Participation: Some instances of the entity may not be involved in the relationship (Optional
participation). Represented by a single line in ERDs.

‫ سواء‬relationship instance ‫ ممكن تشارك بيه ن يف الـ‬entity ‫بعن عن اقل رقم الـ‬
‫ هنا انا ى‬-
.)must(‫) او واحد‬may(‫صفر‬

‫ لكن القسم ممكن يكون لسه‬، ‫ من عند الموظف‬relation ‫ ن يف الـ‬double line ‫يبق هعمل‬‫معي ى‬
‫ن‬ ‫ ن‬-
‫يعن مثال الموظف لزم ع األقل يشتغل تبع قسم‬
‫ى‬ ‫ن‬
.‫ من عند القسم‬one line ‫اتعي لسه يبق هعمل‬ ‫فاتح ومفيش فيه حد‬
‫ ن‬participation ‫ الـ‬-
.weak ‫ من ناحية الـ‬must ‫ بتاعتها لزم يكون‬owner entity ‫ والـ‬weak entity ‫بي الـ‬

‫يعن مثال ن يف حالة الـ‬ ‫ الىل بسأل عنها ى‬rule ‫ واحدة بس بتكرس الـ‬relationship instance ‫ لو عندى‬-
‫ ن‬، may ‫ لزم يكون‬participation ‫يبق هنا الـ‬
‫يبق العالقة هنا لزم‬ ‫الرسكة دة الوحيد الىل مالوش مسئول ى‬
‫الموظفي يكون ليهم مسئول لكن مدير ر‬
‫ن‬ ‫“ المفروض ان كل‬supervise” relationship
.‫ برضو‬may ‫وبالتال العالقة هتكون‬ ‫ن‬ ‫ن‬
‫ وطبعا بالنسبة للناحية التانية مش كل الموظفي لزم يكونوا مسئولي عن حد‬، may ‫تكون‬
‫ي‬
‫ن‬
‫موظفي‬ ‫يعن مثال هل ر‬
‫المرسوع لزم يكون فيه‬ ‫ لما ى‬ternary ‫ بتاع الـ‬participation ‫ ن يف الـ‬-
‫ ن‬، ‫ معايا‬3 entites ‫باج اسأل السؤال بحاول ادخل الـ‬
‫الموظفي لزم يكون عندهم المهارات دى نف ر‬
.‫المرسوع دة‬ ‫ن‬ ‫موظفي نف ر‬
‫ هل‬، ‫مرسوع‬ ‫ن‬ ‫ هل المهارات لزم تكون عند‬، ‫بالمهارات دى‬
‫ي‬ ‫ي‬
 ‫‪ -‬الـ ‪ relationship‬الىل ن‬
‫بي الـ ‪ weak‬والـ ‪ owner‬بتكون اسمها ‪، identifying relationship‬‬
‫وبمنها نان بخىل شكل الـ‪ diamond‬بتاع الـ‪ relationship‬يبق ‪.double-lined‬‬
‫ى‬ ‫ن‬

‫ومعنة عن هدفها ‪ ،‬زى‬

‫ى‬ ‫‪ -‬ساعات بتكون ن يف معلومة او ‪ attribute‬مقدرش اربطها بـ ‪ entity‬لكن بحطها عىل الـ ‪ relationship‬عشان تكون مظبوطة‬
‫بتعن عن‬ ‫امن ‪ ،‬لو جيت حطتها كـ‪ attribute‬عند المدير ى‬
‫هتبق ى‬ ‫بيعن عن الموظف او المدير ىبق بدء يدير اإلدارة دى ى‬
‫مثال ‪ start date‬الىل هو هنا ى‬
‫ى‬ ‫امن المدير بدء يشتغل عموما ‪ ،‬ولو حطيتها عند اإلدارة ى‬
‫يبق معناها اإلدارة بدء الشغل فيها ى‬
‫امن ‪ ،‬يبق لزم احطه عند الـ‪ relationship‬نفسها‪.‬‬ ‫ى‬
How to convert conceptual design to logical design:
.‫ ن يف صورة جداول‬logical design‫ لـ‬ERD‫هنا انا عاوز ىبق احول الرسمة بتاعة الـ‬

Step 1: Mapping of regular entity types

• Create a table for each type of entity.

• Choose one of key attributes to be the primary key.
‫ ن‬، storage‫ بختار الىل بياخد مساحة اقل نف الـ‬primary key‫ ممكن يكون الـ‬attribute ‫اكن من‬
‫يعن لو‬ ‫• نف النقطة الىل فاتت دى لو عندى ى‬
‫ي‬ ‫ي‬
.number ‫ هختار الىل نوعه‬number ‫تان نوعه‬ ‫ و واحد ن‬text ‫ نوعه‬attribute ‫مثال عندى‬
• Every simple attribute is represented by a single column.
• The subparts of the composite attribute are represented by a single column each.
• The multivalued attribute is represented in a single table contains the multi-valued attribute and foreign key
corresponding to the primary key.
The primary key of this new table is the combination of foreign key and another column.
‫ى‬
‫هيبق اداىم حاجة من ن‬
primary ‫ ياما اكرر الـ‬، ‫اتني‬ ‫ جديد لوحده لن‬tuple ‫ مش هينفع اكرره ن يف‬multi-valued attribute‫• ن يف نقطة الـ‬
‫ وساعتها هسيب ى‬multi-valued attribute‫ ياما احط بس الـ‬، primary key‫ابق رابطهم ببعض ودة ضد قواعد الـ‬
‫باف‬ ‫ معاه عشان ى‬key
multi-valued attribute‫ الحل ىبق بيكون نان بعمل جدول منفصل بحط فيه الـ‬، primary key‫الخانات فاضية ودة برضو ضد قواعد الـ‬
.as a Foreign key ‫ بتاع الجدول الرئيس‬primary key‫وبحط معاه الـ‬

• Foreign Key:
o A foreign key is a column or set of columns in one table that refers to the primary key in another
table.
o The foreign key creates a link between the data in the two tables, enforcing referential integrity.
o It ensures that a record in the child table cannot reference a non-existent record in the parent table.
• The derived attribute is not stored in the database by default except when needed.
‫ ن‬attribute ‫ألن وقت ما احتاجه ممكن استنتجه من‬
‫ لكن بخزنه لما بيكون الطلب عليه‬، ‫تان‬ ‫ ن‬derived attribute‫يعن انا مش بخزن الـ‬
‫ن‬ •
‫ى‬
.‫ بخزنه بق‬computing‫ عىل الـ‬headache ‫كبن فبدل ما كل مرة احسبه واعمل‬
 ‫‪Step 2: Mapping of weak entity‬‬

‫•‬ ‫‪The foreign key here is the primary key of the owner entity.‬‬
‫•‬ ‫‪The primary key of the weak entity table is the combination of foreign key and a partial key from the table.‬‬

‫•‬ ‫‪The logical design at the end of the first two steps is:‬‬

‫‪Step 3: Mapping of relationship with (Unary - Binary) degree and (1:N) cardinality‬‬

‫‪• Here, we add the primary key of 1 side as a foreign key to N side.‬‬
‫ألن لو خدت الـ‪ primary key‬بتاع الـ‪ N side‬وحطيته ‪ as foreign key‬نف الـ‪ 1 side‬ى‬
‫يبق كدة خانة‬ ‫طب ليه انا بعمل كدة مش العكس ‪ ،‬ن‬ ‫•‬
‫ي‬ ‫ن‬ ‫ن‬
‫ى‬
‫شغالي يف القسم الواحد ‪ ،‬لكن العكس هيبق صحيح ان الموظف‬ ‫ن‬ ‫ن‬
‫موظفي كتن‬ ‫هتبق ‪ multi-valued‬ألن يف‬ ‫ى‬ ‫الـ‪ SSN‬ن يف جدول الـ‪Dept‬‬
‫يعن مثال ن يف حالة جدول الـ‪ Emp‬انا هكرر عامود الـ‪ SSN‬وعشان متلخطبش هسميه مثال ‪ ، Sup-SSN‬وانا هنا‬ ‫الواحد شغال نف قسم واحد ‪ ،‬ن‬
‫ي‬
‫مش بكرر الداتا ألن الـ‪ SSN‬بتاع الموظف الواحد ى‬
‫هيبق ادامه ‪ SSN‬مختلف بتاع المدير بتاعه‪.‬‬
‫بعن عن معلومة جديدة ‪ ،‬وممكن‬ ‫ن‬ ‫ن‬ ‫ن‬ ‫•‬
‫يف الـ ‪ unary‬بضيف الـ ‪ primary‬تان عادى لنفس الـ ‪ entity‬وانا هنا مش بعمل تكرار للداتا لن هنا ى‬
‫اضيفه باسم متغن عشان ميحصلش لخبطة‪.‬‬
‫لو كان الـ ‪ primary‬أصال موجود ن يف الناحية التانية زى مثال حالة الـ‪ weak entity‬هنا انا مش محتاج اضيفه تان‪.‬‬
‫ن‬ ‫•‬
 ‫‪Step 4: Mapping of relationship with (Unary - Binary) degree and (M:N) cardinality‬‬

‫هنا انا باخد الـ ‪ primary keys‬بتوع الـ‪ entities‬كـ ‪ foreign keys‬ن يف جدول جديد باسم الـ ‪.relationship‬‬ ‫•‬
‫الـ‪ primary key‬بتاع الجدول الجديد هو الـ ‪ combination‬بتاع الـ ‪.foreign keys‬‬ ‫•‬
‫يعن اى ‪ mapping of relationship‬بيطلع منه ‪ foreign key‬فانا‬ ‫لو نف ‪ attribute‬عىل الـ‪ relationship‬دايما بيتبع الـ ‪ ، foreign key‬ن‬ ‫•‬
‫ي‬
‫في وبحط الـ ‪ attribute‬دى معاه‪.‬‬‫بشوف الـ ‪ foreign key‬دة راح ن‬

‫‪Step 5: Mapping of relationship with (Unary - Binary) degree and (1:1) cardinality‬‬

‫•‬ ‫‪Here, there are three possibilities:‬‬

‫‪o May-must‬‬

‫هنا انا باخد الـ‪ primary key‬بتاع الـ‪ may‬وبحطه كـ‪ foreign key‬عند الـ‪.must‬‬ ‫‪o‬‬
‫بي الـ‪ Employee‬والـ‪ Dept‬هاخد الـ‪ primary key‬بتاع المدير الىل هو الـ‪ SSN‬واحطه كـ‪foreign key‬‬‫يعن مثال نف حالة العالقة ‪ Manage‬ن‬ ‫ن‬ ‫‪o‬‬
‫ي‬
‫ن‬
‫الموظفي هيكونوا رؤساء‬ ‫ن‬
‫عند الجدول بتاع الـ‪ Dept‬ألن لزم القسم يكون له مدير فدايما هيكون يف قيمة للـ‪ SSN‬عند الـ‪ ، Dept‬لكن مش كل‬
‫اقسام فلو عملت العكس هيكون ن يف خانات فاضية ن يف جدول الـ‪.Employee‬‬
‫ن‬
‫كان عندى ‪ attribute‬عىل الـ‪ relationship‬فعملت زى القاعدة وخليته يتبع الـ‪ foreign key‬وحطيته يف جدول الـ‪.Dept‬‬ ‫‪o‬‬

‫‪o‬‬ ‫‪May-may‬‬
‫التان كـ ‪ ، foreign key‬او حل تالت نان اعمل جدول جديد بيهم لكن دة مش مفضل‪.‬‬
‫هنا انا ممكن اخد أي ‪ primary key‬واحطه عند ن‬

‫‪o‬‬ ‫‪Mus-must‬‬

‫التني القدام ‪ ،‬ولو ن يف المستقبل حبيت‬

‫ن‬ ‫الجدولي ن يف جدول واحد جديد ‪ ،‬ويكون الـ ‪ primary key‬بتاع الجديد اى واحد من‬
‫ن‬ ‫هنا انا ى‬
‫هبق مضطر ادمج‬
‫اخنته عادى ‪ ،‬وكمان لو عاوز اضيف ‪foreign‬‬ ‫مخنتش الـ‪ primary key‬بتاعه ممكن برضو استخدم الىل انا ى‬ ‫اعمل ‪ relationship‬مع الـ‪ entity‬الىل ى‬
‫ن‬ ‫ن‬
‫الجدولي القدام ممكن اضيفه يف الجديد عادى‪.‬‬ ‫‪ key‬ن يف اى جدول من‬
 Step 6: Mapping of ternary relationship

.‫ ن يف الجدول الجديد‬foreign keys ‫ بتوع الجداول القديمة كـ‬3 primary keys ‫هنا مفيش غن نان اعمل جدول جديد وهحط الـ‬

The final Logical design is:

‫‪Database constraints‬‬
‫‪Restrictions on database table or object to help maintain integrity of data.‬‬

‫‪Referential integrity constraint:‬‬

‫ن‬
‫بمعن انه لزم األول اضيف قسم ببياناته‬ ‫‪ -‬ن يف حالة لو انا بعمل ‪ insert‬لزم األول أكون عامل ‪ parent record‬عشان اقدر اضيف ‪، child record‬‬
‫عشان اقدر لما ىاج اضيف بيانات موظف احط رقم القسم دة‪.‬‬
‫ن‬
‫بمعن انه لزم اغن كل ارقام اقسام‬ ‫‪ -‬ن يف حالة لو انا بعمل ‪ delete‬لزم األول امسح كل الـ ‪ child records‬عشان اقدر امسح الـ ‪، parent record‬‬
‫شغالي ن يف القسم الىل همسحه قبل ما امسح القسم نفسه‪.‬‬
‫ن‬ ‫ن‬
‫الموظفي الىل‬
SQL

Data definition language (DDL)

.‫ نفسها لكن مقدرش اعدل ن يف الداتا نفسها‬database ‫ بتاع الـ‬structure ‫ الىل عن طريقها بقدر اعدل ن يف الـ‬commands ‫ه عبارة عن مجموعة الـ‬
‫ي‬
(CREATE – ALTER – DROP – TRUNCATE - RENAME)
 CREATE: ALTER:
This command is used to create the database or its This command is used to add, delete or change columns
objects (like table, index, function, views, store in the existing table. The user needs to know the existing
procedure, and triggers). table name.

CREATE DATABASE database_name; ALTER TABLE table_name ADD (Columnname_1

datatype, … Columnname_n datatype);
CREATE TABLE table_name
( ALTER TABLE table_name
column_1 datatype constraint, DROP COLUMN column_name;
column_2 datatype constraint,
column_3 datatype constraint,
ALTER TABLE table_name
....
MODIFY COLUMN column_name datatype;
);
DROP: RENAME:
This command is used to remove an existing table It is possible to change the name of table with or without
along with its structure from the Database. data in it using simple RENAME command.

DROP TABLE table_name; RENAME TABLE Table_Name To New_Table_Name;

TRUNCATE:
This command is used to remove all rows from the table, but the structure of the table still exists.

TRUNCATE TABLE table_name;

Data control language (DCL)

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other
controls of the database system.

GRANT: REVOKE:
This command gives users access privileges to the This command withdraws the user’s access privileges
database. given by using the GRANT command.

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
ANOTHER_USER; REVOKE ALL on TABLE department FROM Mary;

‫ ممكن هو كمان يـ‬Ahmed ‫ بـ‬log in ‫اخر واحدة معناها نان لو عملت‬

‫ن‬
‫تانيي‬ users ‫ دى مع‬privilege ‫ الـ‬share
 Data manipulation language (DML)

They are the SQL commands that deal with the manipulation of data present in the database belonging to DML or
Data Manipulation Language and this includes most of the SQL statements.

It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements
are grouped with DML statements.

(SELECT – INSERT – DELETE - UPDATE)

INSERT: (works on record level)
It is used to insert data into a table.

: insert command‫ طرق ممكن اعمل بيهم الـ‬3 ‫عندى‬

‫ بتاعتها ى‬values ‫ كلها وبعدين بكتب الـ‬columns ‫ بكتب أسماء الـ‬.1
‫بالنتيب‬

INSERT INTO Student (Stu_id, Stu_Name, Stu_Marks, Stu_Age) VALUES (104, ‘Anmol’, 89, 19);

columns ‫ علطول لو انا عارف ترتيب الـ‬values ‫ أو بكتب الـ‬.2

INSERT INTO Student VALUES (104, ‘Anmol’, 89, 19);

‫ بس الىل انا عاوزها‬columns ‫ أو لو عاوز اضيف قيم معينة بس لزم اكتب األول أسماء الـ‬.3

INSERT INTO Student (Stu_id, Stu_Age) VALUES (104, 19);

UPDATE: (works on column level)

It is used to update existing data within a table.

UPDATE Table_name SET [column_name1= value_1, ….., column_nameN = value_N] WHERE CONDITION;

UPDATE Product SET Product_Price = 80 WHERE Product_Id = 'P102' ;

UPDATE Student SET Stu_Marks = 80, Stu_Age = 21 WHERE Stu_Id = 103 AND Stu_Id = 202;
.‫ كله مع بعض‬column ‫ لو مكتبتهاش كدة انا هعدل ن يف الـ‬، ‫ الىل انا عاوز اعدل بياناته‬record ‫الرسط او الـ‬
‫ دى الىل بتحددل ر‬where ‫جملة‬

DELETE: (works on table level)

It is used to delete records from a database table.

DELETE FROM Table_Name WHERE condition;

DELETE FROM Student WHERE Stu_Marks > 70 ;

DELETE FROM GFG_EMPLOyees; --------> delete all the records.

‫ن‬
‫الداتابن وشغال‬ ‫ عىل‬access ‫ طول مانا عامل‬-
‫بعدل ن يف البيانات التعديل دة بيحصل عىل‬
‫الميمورى بتاىع بس مش بيحصل تعديل عىل‬
‫ن‬
‫الداتابن غن‬ ‫ الىل عليها‬physical memory ‫الـ‬
.commit ‫لما بعمل‬
‫ دة بياخد كل التعديالت‬commit ‫ لما بعمل‬-
.physical memory ‫الىل عملتها وينقلها عىل الـ‬
‫ اول ما‬auto-commit ‫ بتعمل‬DDL‫ أوامر الـ‬-
.‫بنفذها‬
 SELECT

• It is used to retrieve data from the database.

• Syntax:

SELECT column_Name_1, column_Name_2, ….., column_Name_N FROM Name_of_table;

SELECT * FROM table_name; ---------> retrieve all columns of table.

SELECT Emp_Id, Emp_Salary FROM Employee; ---------> retrieve all the values of a specific column.

SELECT * FROM Student WHERE Stu_Marks = 80; ---------> retrieve all the records of a specific condition.
‫ ن‬columns‫ لو أنا بكتب أسماء الـ‬-
‫ بحط اسمه ن‬، ‫وف واحد منهم اسمه فيه مسافة‬
.square brackets [ column name ] ‫بي‬

SELECT * FROM employees WHERE salary >=2000 AND <= 5000;

BETWEEN

SELECT FullName FROM employees WHERE salary BETWEEN 1500 AND 2500;

IN

SELECT * FROM employees WHERE Supervisor IN (16544,12046); ---------> equals OR operator.

‫يعن بعده بقدر احط ى‬
single-row operator ‫= دى كلها‬, < ,> ‫ عكس حاجات زى الـ‬، ‫اكن من قيمة‬ ‫ ن‬multi-row operator ‫ دة‬IN ‫ عندى هنا الـ‬-
‫ن‬
. ‫يعن لزم بعدها اكتب قيمة واحدة بس‬

LIKE

SELECT * FROM employees WHERE f_name like ‘?o*’;

‫ بالنسبة للـ ? فىه‬، ‫ زى الىل بكتبها‬exact ‫لن ساعتها هكون بدور ع قيمة‬‫ بدل ما استعمل = ن‬match pattern ‫ لما أكون عاوز‬like ‫ هنا انا بستعمل‬-
‫ن‬
.‫معي من صفر لحد مالنهاية‬ ‫بتعن عن عدد حروف‬
‫بتعن عن حرف واحد و الـ * ى‬ ‫ى‬

ALIAS

SELECT f_name+’ ‘+l_name as full_name FROM employees WHERE (salary*12)>10000;

‫ جديد باى شكل‬column ‫بمعن نان عملت‬

‫ن‬ ، Alias ‫ ن يف الجدول فاستعلمت حاجة اسمها‬f_name+’ ‘+l_name ‫ اسمه‬column ‫ هنا انا معنديش‬-
.‫ الجديد دة‬column ‫ يقدر يعرض الـ‬DBMS ‫ زى المثال واديتله اسم عشان الـ‬concatenation ‫ىبق عمليات حسابية او‬

ORDER BY

SELECT * FROM employees ORDER BY f_name asc, salary desc;

‫ لو ه مش ى‬column ‫ عشان اعمل ترتيب لقيم الـ‬order by ‫ بتاعة‬clause ‫ هنا انا بستعمل الـ‬-
‫ فالول انا بقوله رتب األسماء تصاعدى‬، ‫منتبة مثال‬ ‫ي‬ ‫بمعن انه هينفذ اول رشط وبعدين لما ى‬
‫يالف مثال ن‬
.‫اتني اسمهم احمد هنتبهم بالمرتب بتاعهم تنازل‬ ‫ن‬ ، ‫وبعديها رتب المرتبات تنازل‬
‫ ن‬ascending ‫ انه بنتب‬order by ‫ بتاع‬default ‫ الـ‬-
.column ‫ بعد اسم الـ‬asc ‫يعن مش لزم اكتب كلمة‬
DISTINCT

SELECT DISNTICT dep_no FROM employees;

‫ بدون تكرار القيم فيه ى‬column ‫ بتخليه يعرض الـ‬distinct ‫ هنا كلمة‬-
.‫وبنتبهم تصاعدى كمان‬

SELECT DISNTICT dep_no, f_name FROM employees;

distinction ‫ه بتعمل‬ ‫ن‬ ‫ن‬

‫ سوى يعن ي‬columns ‫ لالتني‬combination ‫ بدون تكرار القيم بتاعة الـ‬column ‫ بتخليه يعرض الـ‬distinct ‫ هنا كلمة‬-
.‫ الىل رجعت‬result set‫للـ‬

JOINING

• In SQL, joins are used to combine rows from two or more tables based on a related column.
• There are different types of joins, including INNER JOIN, OUTER JOIN, and EQUI JOIN.
‫ نف نفس الوقت بيكون نف رابط ن‬، ‫اكن من جدول مش جدول واحد‬
‫بي الجداول‬ ‫ عموما هو نان عاوز اجيب داتا موجودة نف ى‬joining‫فكرة الـ‬ •
‫ي‬ ‫ي‬ ‫ي‬
.foreign key‫ والـ‬primary key‫ الىل هو غالبا بيكون الـ‬، ‫دى‬

1- EQUI JOIN

• An EQUI JOIN is a type of join that uses the equality operator (=) to match rows between tables.
• The result set includes only those rows where the specified columns have matching values.
SELECT fname, depName FROM employees, departments WHERE id=mngID;

‫ منهم موجود ن يف‬column ‫ لكن كل‬، ‫لن عاوز اجيب أسماء المديرين وأسماء األقسام بتاع كل واحد فيهم‬
‫ ن‬، Equal join ‫ هنا انا بستعمل حاجة اسمها‬-
‫ يكون هو نفسه‬id ‫ عشان أقوله اربط كل اسم بقسمه عن طريق ان‬join ‫ فاستعملت الـ‬، ‫وف نفس الوقت ملهمش عالقة ببعض‬ ‫جدول مختلف ن‬
.mngID
.‫ بيكون عدد الـجداول ناقص واحد‬join conditions ‫ عدد الـ‬-
‫ بتكون عالقة ن‬join condition ‫ الـ‬-
.‫ بتاعه‬foreign key ‫ والـ‬primary key ‫بي الـ‬

SELECT fname, depName FROM employees, departments WHERE employees.dno=departments.dno;

‫ن‬
.).( ‫الجدولي عشان كدة استعملت‬ ‫ له نفس السم ن يف‬column ‫ الرابط بينهم هو‬، ‫شغالي فيها‬
‫ن‬ ‫ن‬
‫الموظفي وأسماء اإلدارات الىل‬ ‫هنا انا بعرض أسماء‬

SELECT fname, depName FROM employees e, departments as d WHERE e.dno=d.dno;

‫ن‬
.as ‫بطريقتي ياما بسيب مسافة وبكتب السم الجديد او بستعمل‬ ‫ وعملتها‬، ‫ لسماء الجداول عشان اسهل عىل نفس‬alias ‫هنا انا عملت‬

SELECT fname, projName, hours FROM employees, project, works_for

WHERE ssn=essn AND pno=pnumber;

‫يعن لزم يكون‬ ‫شغالي فيها وعدد الساعات الىل اشتغلوها نف ر‬

‫ جداول ن‬3 ‫ كدة انا عندى‬، ‫المرسوع‬ ‫ن‬ ‫ن‬
‫الموظفي وأسماء المشاري ــع الىل‬ ‫هنا انا بعرض أسماء‬
‫ي‬
.‫ جداول‬3‫ من الـ‬2 foreign keys ‫ وربطهم بـ‬2 primary keys ‫ فدورت عىل‬، 2 join conditions ‫عندى‬

2- INNER JOIN
‫ ر‬keyword ‫ انا هستعمل‬، join ‫هنا ىبق انا مش هستعمل الـ = عشان اعمل‬
.joining‫مباشة تعمل عملية الـ‬

SELECT fname, depName

FROM employees e inner join departments as d
ON e.dno=d.dno;

‫ مفيش فرق‬EQUI JOIN‫زى الـ‬

3- OUTER JOIN

• An OUTER JOIN returns all rows from one table and the matching rows from the other table.
• If there’s no match, NULL values are returned for columns of the table that lacks a match.
‫ ن‬، condition ‫ من الـ‬matching ‫حن لو محصلش‬
:‫ أنواع‬3 ‫وف منها‬ ‫ بتختلف نف انها بتعرضىل كل الداتا ى‬outer join‫ هنا الـ‬-
‫ي‬

A- LEFT OUTER JOIN (or LEFT JOIN):

• Returns all rows from the left table and the matching rows from the right table.
• If there's no match, NULLs are returned for the columns of the right table.

SELECT fname, depName

FROM employees e LEFT OUTER JOIN departments d
ON e.dno=d.dno;

‫ وساعتها هيحط ادام اسم‬، ‫شغالي ن يف اقسام‬

‫ن‬ ‫الموظفي ى‬
‫حن لو مش‬ ‫ن‬ ‫ ن‬، ‫ الىل ع الشمال‬column ‫ بتاعة الـ‬full data ‫هنا هو بياخد الـ‬
‫يعن هيجيب كل‬
.NULL ‫الموظف‬

B- RIGHT OUTER JOIN (or LEFT JOIN):

• Returns all rows from the right table and the matching rows from the right left.
• If there's no match, NULLs are returned for the columns of the left table.

SELECT fname, depName

FROM employees e RIGHT OUTER JOIN departments d
ON e.dno=d.dno;
‫ن‬
‫ وساعتها هيحط ادام اسم‬، ‫موظفي فيها‬ ‫يعن هيجيب كل القسام ى‬
‫حن لو مفيش‬ ‫ ن‬، ‫اليمي‬
‫ن‬ ‫ الىل ع‬column ‫ بتاعة الـ‬full data ‫هنا هو بياخد الـ‬
.NULL ‫القسم‬
 C- FULL OUTER JOIN (or FULL JOIN):

• Returns all rows when there is a match in either table.

• If there is no match, NULL values are returned for the columns of the table that lacks a match.

SELECT fname, depName

FROM employees e FULL OUTER JOIN departments d
ON e.dno=d.dno;
‫ ن‬، ‫ لىل موجود‬matching ‫الموظفي وهيعمل‬
‫يعن األول مثال‬ ‫ن‬ ‫ ن‬، 2 columns ‫ بتاعة الـ‬full data ‫هنا هو بياخد الـ‬
‫يعن هيجيب كل القسام وكل‬
‫ن‬
.‫ ونفس الكالم مع األقسام‬، NULL ‫الموظفي وبعد كل موظف لو موجود له قسم هيكتب اسم القسم لو مش موجود هنجع‬ ‫هيكتب كل أسماء‬

4- Self Join

• A self-join is a type of join in SQL where a table is joined with itself.

• This is useful when you need to:
o Compare rows within the same table.
▪ For example, finding the manager of an employee within the same Employees table.
o Find relationships between different rows in the same table.
▪ For example, comparing records in the same table, like finding pairs of employees with the
same salary.
• When you perform a self-join, you treat the table as if it were two separate tables by using table aliases.
• These aliases allow you to distinguish between the two instances of the table in your query.

SELECT e.fname, s.fname

FROM employee e, employee s
WHERE e.supID=s.id;

‫ فظهرت‬، ‫ انا عاوز اعرض اسم كل موظف واسم المدير بتاعه‬، recursive relationship ‫ ودة غالبا بتكون مع جدول فيه‬self join ‫ هنا انا بستخدم‬-
‫ جبت‬join ‫وف رشط الـ‬‫ ن‬، ‫للموظفي ونسخة للمديرين‬
‫ن‬ ‫كأن عملت نسخة‬ ‫ للجدول ن‬alias ‫ فعملت‬، ‫تي‬ ‫ مر ن‬column ‫عندى مكلة نان عاوز اعرض نفس الـ‬
.‫ بتاع المدير من جدول المدير‬id ‫ بتاع المدير بتاع الموظف من جدول الموظف وربطته بالـ‬id ‫الـ‬
‫وف ر‬‫ن‬ ‫ن‬ ‫ن‬ ‫ن‬ ‫ هنا انا بحاول اشوف ن‬-
‫ بتاع‬primary key‫ بجيب الـ‬join‫الرسط بتاع الـ‬ ، ‫النسختي بتوع الجدول الىل بعملهم‬ ‫ يف‬child‫ومي الـ‬ parent‫مي الـ‬
‫ى‬ ‫ى‬
‫ ألنه المدير الواحد له اكن من موظف تحته لكن‬parent‫ هو الـ‬Supervisor‫ فمثال هالف ان الـ‬، child‫ بتاع الـ‬foreign key‫ واساويه بالـ‬parent‫الـ‬
.‫الموظف له مدير واحد‬
‫ نف الغالب دايما بيكون الجدول الىل موجود عندى نف الداتا ن‬-
.parent‫ والجدول النسخة منه الىل بيتعمل جديد بيكون هو الـ‬child‫بن أصال هو الـ‬ ‫ي‬ ‫ي‬

Sub-queries

SELECT * FROM employees

WHERE salary > ( SELECT salary FROM employees WHERE fname=’Ahmed’ AND lname=’Ali’);

‫ وعشان المرتب دة معلومة مش عندى جاهزة انا لسه هجيبها من جوة الجدول فاستخدمت‬، ‫اكن من مرتب احمد عىل‬ ‫ن‬
‫الموظفي الىل مرتبهم ى‬ ‫ هنا انا عاوز اعرض كل‬-
.nested-queries ‫ او‬sub-queries
Multi-row Operators: (IN – ALL – ANY)

• In SQL, multi-row operators like ALL and ANY are used in conjunction with subqueries to compare a value to
a set of values returned by the subquery.
• These operators allow you to perform comparisons between a single value and multiple values.

1. ALL Operator

• The ALL operator compares a value to every value in a list or subquery.

• The condition must be true for all values in the list for the entire condition to be true.
• Syntax:

expression operator ALL (subquery)

• Where:
o expression: The value or column you're comparing.
o operator: A comparison operator, such as =, >, <, >=, <=, <>.
o subquery: A subquery that returns a set of values.
• Example: Find employees who have a salary greater than all salaries in the Sales department.

SELECT *
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Sales');

2. ANY Operator

• The ANY operator compares a value to each value in a list or subquery.

• The condition must be true for at least one value in the list for the condition to be true.
• Syntax:

expression operator ANY (subquery)

• Where:
o expression: The value or column you're comparing.
o operator: A comparison operator, such as =, >, <, >=, <=, <>.
o subquery: A subquery that returns a set of values.
• Example: Find employees who have a salary greater than any salary in the Sales department.

SELECT *
FROM Employees
WHERE Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'Sales');

Example to Illustrate Both:

Consider a scenario where you want to find employees whose salary is higher than the highest salary in the Sales
department (ALL) and whose salary is higher than at least one salary in the HR department (ANY):

SELECT *
FROM Employees
WHERE Salary > ALL (SELECT Salary FROM Employees WHERE Department = 'Sales') AND
Salary > ANY (SELECT Salary FROM Employees WHERE Department = 'HR');
SQL Aggregate Functions: ( MAX() – MIN() – AVG() – COUNT() - SUM() )

- SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It
returns a single value.

COUNT()

- COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-
numeric data types.

- COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers
duplicate and Null.

• Count(salary): Return number of Non-null values over the column salary.

• Count(Distinct Salary): Return number of distinct Non-Null values over the column.

SUM()

- Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

• sum(salary): Sum all Non-Null values of Column salary.

• sum(Distinct salary): Sum of all distinct Non-Null values.

AVG()

- The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of
all non-null values.

MAX()

- MAX function is used to find the maximum value of a certain column. This function determines the largest value of
all selected values of a column.

• Max(salary): Maximum value in the salary column except NULL.

MIN()

- MIN function is used to find the minimum value of a certain column. This function determines the smallest value of
all selected values of a column.

• Min(salary): Minimum value in the salary column except NULL.

-- Find the maximum order amount
SELECT MAX(TotalAmount) AS MaxOrder
FROM Orders;

-- Find the minimum order amount

SELECT MIN(TotalAmount) AS MinOrder
FROM Orders;

-- Find the average order amount

SELECT AVG(TotalAmount) AS AvgOrder
FROM Orders;
-- Count the total number of orders
SELECT COUNT(*) AS NumberOfOrders
FROM Orders;

-- Find the total sum of all orders

SELECT SUM(TotalAmount) AS TotalSales
FROM Orders;

‫ ن‬column ‫ اى‬Select‫يبق انا مينفعش اعرض جنبها نف الـ‬

‫تان ألن مش هينفع احط قيمة‬ ‫ ىبنجع قيمة واحدة بس ى‬aggregate functions‫ بما ان الـ‬-
‫ي‬
‫ ن‬column‫ الـ‬Group by ‫ بالتال انا مجن نان اعمل‬، ‫واحدة جنب كذا قيمة‬
: ‫ الىل جاية دى‬query‫ زى الـ‬، ‫التان الىل عاوز اعرضه دة‬ ‫ى‬
SELECT Min(Salary), Dept_ID
FROM Employee
GROUP BY Dept_ID
‫ن‬
.‫موظفي عن طريق القسم بتاعهم وبجيب اقل مرتب جوة كل قسم‬ ‫هنا انا جمعت كل مجموعة‬

GROUP BY & HAVING

• The GROUP BY and HAVING clauses in SQL are used together to group rows that share certain properties and
filter the results based on aggregate functions. They are often used to organize and summarize data.
• The GROUP BY clause is used to group rows that have the same values in specified columns into summary
rows.
• The HAVING clause is used to filter the results after the GROUP BY clause has been applied.
• It’s similar to the WHERE clause, but WHERE filters rows before grouping, while HAVING filters groups after
aggregation.
• The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate
functions.
• In the query, the GROUP BY clause is placed after the WHERE clause.
• In the query, the GROUP BY clause is placed before the ORDER BY clause if used.
• In the query, the Group BY clause is placed before the Having clause.
• Place condition in the having clause.
‫بتمس عىل كل صف وتطبق‬ ‫ر‬ ‫ كله ن‬Column‫ بتشتغل عىل الـ‬WHERE ‫ ألن الـ‬Aggregate Functions ‫ مع‬WHERE ‫• مينفعش استخدم‬
‫يعن‬
.HAVING ‫ فعشان كدة بستخدم معاها‬one value ‫ ىبنجع‬Aggregate Function‫ لكن الـ‬Condition‫الـ‬
row ‫ عىل كل‬Condition‫ ىبنوح تنفذ الـ‬WHERE‫ ألن هنا الـ‬WHERE SUM(Salary) > 1000 ‫بمعن نان مينفعش اكتب حاجة زى كدة‬ ‫ن‬ •
‫ن‬ ‫ى‬
.‫ كامل‬Column ‫ واحد المفروض انها بتتعمل عىل‬row ‫ عىل‬Aggregate function ‫فمش منطق ان اعمل‬
:‫يعن مثال ن يف المثال الىل جاى دة‬
‫ ن‬، query ‫ نف اى‬aggregate functions‫• بس دة برضو مش معناه نان مينفعش استعملها خالص مع الـ‬
‫ي‬
SELECT COUNT(Eid), Address
FROM Emplyee
WHERE Did IN (10,30)
GROUP BY Address

‫ هيتشالوا‬20 ‫يعن كل الناس الىل ن يف قسم رقم‬

‫ ن‬، GROUP BY‫ قبل ما يتبعت للـ‬column‫ بتشتغل عىل الـ‬WHERE‫هنا الـ‬
‫ن‬
.GROUP ‫ تبدء تعد جوة كل‬COUNT‫ وبعدين الـ‬30 ‫ و‬10 ‫القسمي‬ ‫ هتجمع‬GROUP BY‫ وبعد كدة الـ‬result set‫من الـ‬

SELECT AVG(salary) FROM employees GROUP BY depNum HAVING MAX(salary)>10000;

.10000 ‫اكن من‬ ‫ن‬ ‫الموظفي بتوع كل قسم لوحده ر‬
‫ن‬
‫اكن مرتب يف القسم ى‬
‫برسط ان يكون ى‬ ‫ هنا هو بيعرض متوسط المرتبات بتاع‬-
Example:

Imagine you have an Orders table with columns OrderID, CustomerID, OrderDate, and TotalAmount. You want to
find customers who have made more than 5 orders and spent more than $10,000 in total.

SELECT CustomerID, COUNT(OrderID) AS NumberOfOrders, SUM(TotalAmount) AS TotalSpent

FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5 AND SUM(TotalAmount) > 10000;

Explanation:

• GROUP BY CustomerID: Groups the rows by each CustomerID.

• COUNT(OrderID): Counts the number of orders for each customer.
• SUM(TotalAmount): Calculates the total amount spent by each customer.
• HAVING: Filters out customers who haven’t made more than 5 orders or spent more than $10,000.
REGEXP:
• In MySQL, REGEXP is a pattern-matching operator that allows you to search for strings that match a regular
expression pattern.
• It's similar to the LIKE operator but offers more flexibility with complex patterns.
• Regular expressions (regex) are a powerful way to define search patterns for text.
• Syntax:

SELECT column_name
FROM table_name
WHERE column_name REGEXP 'pattern';

Dot (.)

• Matches any single character.

SELECT 'abc' REGEXP 'a.c'; -- Returns 1 (true), matches "a" followed by any character, then "c"

Caret (^)

• Matches the start of a string.

SELECT 'abc' REGEXP '^a'; -- Returns 1 (true), matches strings starting with "a"

Dollar ($)

• Matches the end of a string.

SELECT 'abc' REGEXP 'c$'; -- Returns 1 (true), matches strings ending with "c"

Asterisk (*)

• Matches zero or more occurrences of the previous character or group.

SELECT 'aaab' REGEXP 'a*b'; -- Returns 1 (true), matches zero or more "a"s followed by "b"

Plus (+)

• Matches one or more occurrences of the previous character or group.

SELECT 'aaab' REGEXP 'a+b'; -- Returns 1 (true), matches one or more "a"s followed by "b"

Question Mark (?)

• Matches zero or one occurrence of the previous character or group.

SELECT 'ab' REGEXP 'a?b'; -- Returns 1 (true), matches zero or one "a" followed by "b"

Character Class ([])

• Matches any single character within the brackets.

SELECT 'abc' REGEXP '[ab]'; -- Returns 1 (true), matches either "a" or "b"

Negated Character Class ([^])

• Matches any single character not within the brackets.

SELECT 'abc' REGEXP '[^ab]'; -- Returns 1 (true), matches any character except "a" or "b"
Execution sequence of SELECT:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Order Clause Function Notes

The FROM clause, and subsequent JOINs are first executed to determine
the total working set of data that is being queried. This includes
FROM and Tables are joined to
1 subqueries in this clause and can cause temporary tables to be created
JOIN get the base data. under the hood containing all the columns and rows of the tables being
joined.
Once we have the total working set of data, the first-pass WHERE
constraints are applied to the individual rows, and rows that do not
satisfy the constraint are discarded. Each of the constraints can only
The base data is
2 WHERE access columns directly from the tables requested in the FROM clause.
filtered. Aliases in the SELECT part of the query are not accessible in most
databases since they may include expressions dependent on parts of the
query that have not yet executed.
The remaining rows after the WHERE constraints are applied are then
grouped based on common values in the column specified in the GROUP
The filtered base data BY clause. As a result of the grouping, there will only be as many rows as
3 GROUP BY
is grouped. there are unique values in that column. Implicitly, this means that you
should only need to use this when you have aggregate functions in your
query.
If the query has a GROUP BY clause, then the constraints in the HAVING
The grouped base clause are then applied to the grouped rows, discard the grouped rows
4 HAVING
data is filtered. that don't satisfy the constraint. Like the WHERE clause, aliases are also
not accessible from this step in most databases.
The final data is
5 SELECT Any expressions in the SELECT part of the query are finally computed.
returned.
If an order is specified by the ORDER BY clause, the rows are then sorted
The final data is by the specified data in either ascending or descending order. Since all
6 OREDER BY
sorted. the expressions in the SELECT part of the query have been computed, you
can reference aliases in this clause.
Finally, the rows that fall outside the range specified by the LIMIT and
LIMIT / The returned data is
7 OFFSET are discarded, leaving the final set of rows to be returned from
OFFSET limited to row count. the query.
.GROUP BY ‫ األول جوة‬mention ‫ لزم تكون معمول ليها‬ORDER BY ‫ أو الـ‬SELECT ‫ عشان اقدر استعمل اى حاجة جوة الـ‬-

Example (1):
Display the department name and the maximum salary for each department given that its average salary is greater
than 1200, then sort the result by department name.
SELECT dname, MAX(salary) as max
FROM employee e, departments d
WHERE e.dno=d.dno
GROUP BY dname
HAVING AVG(salary) > 1200
ORDER BY dname
 ‫ترتيب تنفيذ الـ‪:query‬‬
‫كأن بروح اجيب الجداول الىل قولت عليها واسجلهم ن يف الميمورى‪.‬‬
‫‪ -1‬اول حاجة بتتنفذ ه الـ‪ from‬ودة ن‬
‫ي‬

‫ن‬
‫للجدولي‬ ‫يعن بروح اشوف ايه الـ‪ conditions‬الىل كاتبها ادامها وبنفذها ‪ ،‬هنا مثال انا بعمل ‪EQUI JOIN‬‬ ‫ن‬
‫تان حاجة بتتنفذ ه الـ‪ ، where‬ن‬ ‫‪-2‬‬
‫ي‬
‫موظفي ن يف جدول الـ‪ employee‬مفيش ادامهم ارقام إدارة فـ اتشالوا من‬
‫ن‬ ‫فكأنهم اتحطوا جوة جدول واحد ‪ ،‬وبما نان عملت ‪ join‬وكان ن يف‬
‫الجدول الجديد‪.‬‬

‫الرسط الىل انا كاتبه (الىل هو ن يف المثال ‪)dname‬‬

‫يعن بنوح عىل كل مجموعة ‪ records‬شبه بعض نف ر‬
‫ي‬
‫‪ -3‬بعد كدة بينفذ الـ‪ ، group by‬ن‬
‫ويحطهم نف جروب لوحدهم وكأنه بيحط عليهم ‪ label‬ر‬
‫بالرسط الىل انا عملت ليهم جروب بيه‪.‬‬ ‫ي‬

‫‪ -‬ومن اللحظة دى ىبق الـ‪ DBMS‬معدش شايف داتا جوة الجدول غن الىل انا كنت ذاكرها جوة الـ‪ GROUP BY‬بس ‪ ،‬ن‬
‫يعن معدش شايف غن‬
‫الـ‪.dname‬‬
 ‫يعن هينفذ الىل جوة ‪ HAVING‬والىل جوة ‪ SELECT‬ويروح يحطهم ن يف‬
‫‪ -4‬بعد كدة بنوح ينفذ أي ‪ aggregate function‬موجودة عندى ‪ ،‬ن‬
‫الـ‪ label‬بتاع كل جروب‪.‬‬

‫ى‬
‫بتاعن‪.‬‬ ‫الرسط فكدة هيتشال من الـ‪result set‬‬ ‫ى‬
‫فهالف ان ‪ DP2‬مش محقق ر‬ ‫ر‬
‫ينفذالرسط بتاع الـ‪ HAVING‬نفسها ‪،‬‬ ‫‪ -5‬بعد كدة هنوح‬

‫‪ -6‬بعد كدة هنوح ينفذ الـ‪ ، SELECT‬فهيشوف انا طالب منه يعرض ايه ويعمل بيه ‪ result set‬جديدة ‪ ،‬ولزم الىل انا طالبه يعرضه دة يكون‬
‫يعن من الخر لزم أكون كاتبه جوة الـ‪ GROUP BY‬او حاسبه بـ‪ ، aggregate function‬ن‬
‫يعن لو مثال‬ ‫موجود جوة الـ‪ label‬بتاع كل جروب ‪ ،‬ن‬
‫عاوز اعرض الـ‪ dno‬لزم أكون عامله ‪ mention‬جوة الـ‪.GROUP BY‬‬

‫‪ -7‬اخر حاجة ىبق هنوح ينفذ الـ‪ ORDER BY‬وبرضو لزم يعمل اوردر بحاجة تكون موجودة جوة الـ‪ label‬ى‬
‫حن لو انا مش عارضها جوة‬
‫الـ‪ ، SELECT‬فهيكون شكل الـ‪ query‬النهائية كدة‪:‬‬
Views
• In SQL, a view is a virtual table (logical table) based on the result-set of an SQL statement (SELECT).
• A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real
tables in the database.
• A View can either have all the rows of a table or specific rows based on certain conditions.
• You can add SQL statements and functions to a view and present the data as if the data were coming from
one single table.
• A view contains no data of its own, but it’s like a window through which data from tables can be viewed or
changed.
• The tables on which a view is based are called the base tables.
• The views is stored as a SELECT statement in the data dictionary.
• A view always shows up-to-date data! The database engine recreates the view, every time a user queries it.

‫ فدة‬aggregations ‫ و‬functions ‫ بتاىع بيكون معمول فيه‬View‫ ألن الـ‬DML Operations ‫ مش دايما هقدر اعمل‬Complex Views‫ ن يف حالة الـ‬-
.‫ الصلية‬tables‫معناه ان الداتا الىل ظاهرة دى أصال مش حقيقية او مش زى الداتا الىل ن يف الـ‬

‫ دة الـ‬view‫ عىل الـ‬DML ‫ دة معناه نان لما ىاج اعمل اى‬، view with check option ‫ هنا انا عملت‬-
‫ بيتحقق كل مرة من ر‬DBMS
‫ هو نفسه معندوش‬view ‫ وطبعا بما ان الـ‬، ‫ قبل ما يعمله‬where‫الرسط بتاع الـ‬
.base tables‫ بيقوم واخدها ورايح يعملها عىل الـ‬DBMS ‫ الـ‬DML ‫ فلما ىاج اعمل اى‬data of its own
 ‫ له نفس السم بيعمل واحد‬view ‫ هنا هو بيشوف األول لو مفيش‬-
‫ لن مينفعش ن‬، ‫ ولو نف واحد أصال بيعدل فيه‬، ‫جديد‬
views ‫اتني‬ ‫ي‬
.‫يكون ليهم نفس السم‬

Advantages of Views:
1- Restricting data access – By creating views, you can restrict access to specific rows or columns of a table,
ensuring that users only see the data they are authorized to access.

.table‫ دى عىل أجزاء من الـ‬restrictions ‫ لكن انا هنا ممكن اقسم كمان الـ‬، ‫ كله‬table ‫ عىل الـ‬restrictions ‫ بتعمل‬DCL ‫ودة ألن الـ‬

2- Simplify commands for the user – Views can present a simplified, business-focused schema to end-users or
applications, hiding the complexity of the underlying database structure.
3- Store complex queries – Views allow you to encapsulate complex SQL queries in a simplified and reusable
way. Once a view is created, users can query the view as if it were a simple table, without needing to
understand or repeatedly write the underlying complex SQL logic.
4- Reusability Across Applications – A view can be reused in multiple queries, reports, or applications, ensuring
consistency in data retrieval and logic.
This reduces code duplication and maintenance efforts that If the business logic changes, you only need to
update the view definition rather than changing multiple queries scattered across the application.
5- Consistency Across Changes – If the underlying data changes, the view dynamically reflects the updated
data, ensuring that users always see the most current information.
6- Multiple view facility – Different views can be created on the same table for different users.
7- Data Independence – Views allow you to present a logical view of the data that is independent of how the
data is actually stored in the underlying tables.
This means that changes to the physical schema (e.g., adding or removing columns, splitting or merging
tables) can be made without requiring changes to the queries that users or applications use, as long as the
view definitions are appropriately adjusted.
Index
• An index is a schema object.
• It is used by the server to speed up the retrieval of rows by using a pointer.
• It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly.
• An index helps to speed up select queries and where clauses, but it slows down data input, with the update
and the insert statements (DMLs).
• Indexes can be created or dropped with no effect on the data.
• Indexes is used to solve stored data problems:
o Not sorted
o Scattered: data are not stored in consecutive spaces on physical memory.

‫ من األول لحد ما ى‬index ‫بيمس عىل الـ‬

‫يالف كلمة‬ ‫ر‬ DBMS ‫ الـ‬London ‫ بتاعة‬records ‫ فمثال لو انا عاوز ادور عىل الـ‬، ‫ هنا بيرسع األداء‬index ‫ الـ‬-
، ‫ فهو مش محتاج يكمل خالص‬London ‫ بتاعة‬records‫ تتغن كدة هو عرف ان دى كل الـ‬London ‫ماش لحد ما كلمة‬ ‫ وبعدين يفضل ر‬London
‫ى‬
.‫ الىل عاوزها‬records ‫ لحد ما الف كل الـ‬full-table scan ‫ألن ساعتها بعمل‬‫عكس الطريقة العادية ن‬

‫ الىل موجودة ن يف الجدول‬records‫ الـ‬، ‫حاجتي‬

‫ن‬ ‫ئ‬
‫ شوية لنه ساعتها بيشتغل عىل‬DML‫بيبط أوامر الـ‬ ‫ وهو انه‬، index‫ لكن ن يف عيب برضو عند الـ‬-
‫ن‬ ‫ن‬ ‫ ن‬، ‫ نفسه‬index‫الصىل والـ‬
sorting ‫ وبعدين اروح اعمل‬index‫ األول وبعدين يف الـ‬table‫ فانا هعدله يف الـ‬record‫ لـ‬update ‫يعن لو انا مثال بعمل‬
.performance‫ للـ‬slow down ‫ فدة كله بيعمل‬، ‫ماش مع المعلومات الجديدة‬ ‫ عشان ى‬index‫للـ‬
‫يبق ر‬
Normalization of data:
• Normalization is a database design technique that reduces data redundancy and eliminates undesirable
characteristics like Insertion, Update and Deletion Anomalies.
• Normalization rules divide larger tables into smaller tables and link them using relationships.
• The purpose of Normalization is to eliminate redundant (repetitive) data and ensure data is stored logically.

Data Redundancy

• Redundant data means storing the same piece of data in multiple places.
• This not only wastes storage space but also creates potential issues with data consistency.
• If data is duplicated and one instance is updated while others are not, it leads to inconsistency.
• Normalization helps reduce redundancy by breaking down data into separate tables, ensuring that each
piece of information is stored only once.

، 5 ‫شغالي ن يف قسم رقم‬

‫ن‬ ‫ى‬
‫ موظف‬100 ‫هالف ان لو مثال عندى‬ EMP-DEPT‫ لو جيت بصيت ن يف جدول الـ‬، ‫مشكلة مثال زى الىل ن يف الصورة‬ •
.‫ مرة كمان‬100 ‫هبق محتاج نان اكتب اسم القسم ورقم مدير القسم‬ ‫كدة انا ى‬

Data Modification Anomalies

• Data modification anomalies refer to potential problems that can occur when inserting, updating, or deleting
data in a database that is not properly normalized.

Insertion Anomaly

• An insert anomaly occurs when certain attributes cannot be inserted into the database without the presence
of other attributes.
‫يعن مش هقدر اضيف اى بيانات عن‬ ‫ى‬
‫ ن‬، ‫ بتاع الجدول‬primary key‫ دة الـ‬SSN‫هالف ان الـ‬ ‫ن‬
EMP-DEPT‫يعن مثال لو بصيت عىل جدول الـ‬ •
‫ن‬
.‫متعي فيه‬ ‫ن‬ ‫ن‬
‫قسم معي غن لما يكون يف موظف‬
 Update Anomaly

• An update anomaly occurs when data is duplicated in multiple rows, and a change in one instance requires
the same change to be made in all instances.
• Failure to update all copies leads to inconsistencies.
100 ‫ موظف فانا كدة عاوز اعدل المعلومة دى ن يف الـ‬100 ‫ لو القسم دة فيه‬، )Dmgr_SSN( ‫معي‬
‫ن‬ ‫ن‬
‫يعن لو مثال عاوز اعدل رقم رئيس قسم‬ •
‫ن‬
.‫ بتوع موظفي القسم دة‬row

Deletion Anomaly

• A delete anomaly occurs when the deletion of data representing one fact results in the unintended loss of
other data.
‫ن‬
‫ بتاع الموظف دة كدة انا مسحت بيانات القسم‬record‫ لو جيت مسحت الـ‬، ‫ دة شغال فيه موظف واحد بس‬1 ‫يعن مثال عندى القسم رقم‬ •
‫ن‬
‫ بتاع‬deletion‫ فكدة وجود القسم معتمد عىل وجود الموظف وهيعمىل مشكلة يف الـ‬، ‫يعتن القسم قفل ومعدش موجود عندى‬ ‫ن‬
‫ يعن ى‬، ‫كمان‬
.‫الموظف‬
Functional Dependency

• Functional Dependency is a constraint between two columns or two sets of columns.

• Functional Dependency occurs when the value of one attribute (or a set of attributes) uniquely determines
the value of another attribute (or set of attributes).
• If an attribute A functionally determines B, then it is written as A → B.
• For Example:
o employee_id → name, employee_id functionally determines the name of the employee.
o {student_id, time} → {lecture_room}, student ID and time determine the lecture room where the
student should be.

Full Functional Dependency

• A functional dependency X →Y is full if Y is functionally dependent on X and not on any proper subset of X.
‫بمعن ن‬
، SSN‫ معتمد اعتماد كىل عىل وجود الـ‬Bdate‫ زى مثال وجود الـ‬، Non-key attribute is fully dependent on the key ‫تان‬ ‫ن‬ ‫• او‬
.{SSN,Pnumber} ‫ معتمد اعتماد كىل عىل‬Hours‫او وجود الـ‬

Partial Functional Dependency

• A functional dependency X →Y is partial if Y is functionally dependent on a part of the composite key X.

‫بمعن ن‬
‫ بس‬Pnumber ‫ معتمد عىل‬Plocation‫ زى مثال الـ‬، Composite key‫ معتمد عىل جزء من الـ‬Non-key attribute ‫تان عندى‬ ‫ن‬ ‫• او‬
.Composite Key {SSN,Pnumber}‫الىل هو جزء من الـ‬

Transitive Functional Dependency

• A functional dependency is transitive if there is an indirect relationship between attributes.

• This occurs when X →Y and Y →Z, then X →Z is a transitive dependency.
‫ معتمد عىل‬Dname‫ زى مثال الـ‬، Key‫ معتمد عىل الـ‬Non-key attribute ‫ معتمد عىل‬Non-key attribute ‫تان عندى‬ ‫بمعن ن‬
‫ن‬ ‫او‬ •
.SSN‫ الىل معتمد عىل الـ‬Dnumber
 Normal Forms

• Normalization is achieved through a series of steps called normal forms.

• Normal forms are guidelines used to structure a database in such a way that it reduces redundancy and
ensures data integrity.
• Each normal form has specific rules that must be followed.
• Each normal form builds upon the previous one (Sequential).

• However, higher levels of normalization can lead to more complex database designs and queries. It is
important to strike a balance between normalization and practicality when designing a database.

First Normal Form (1NF)

• A table will be 1NF if it contains only atomic (indivisible) values.

• Ensures that the table structure is free of repeating groups, multi-valued attributes and composite
attributes.

.(Multivalued attribute – Repeating group – Composite Attribute) ‫ لزم ميكونش فيها‬1NF ‫بتاعن ن يف الـ‬
‫ى‬ relation ‫ عشان أقول أن الـ‬-

‫( دول كل واحدة فيهم لوحدها‬Subj , Subj-Desc , G) ‫ والـ‬، multi-valued attribute ‫ دة‬Tel‫ ن يف المثال بتاىع أنا عندى ال ـ‬-
.repeating group ‫ سوى فيه عالقة بينهم اذا هما‬3‫ لكن الـ‬Multi-valued attribute
‫ى‬
.separate relations ‫بتاعن لـ‬ ‫ ى‬-
relation‫ لزم اكرس الـ‬1NF ‫يبق علشان اخليهم عىل الـ‬
Repeating Group:

• A repeating group occurs when a database table has a field (or set of fields) that stores multiple related
values for a single entity or record.
• This is often seen when a table includes fields that hold lists or arrays of values, rather than representing
each value as a separate record in a related table.

.composite primary key ‫ دول مع بعض‬Subject ‫ و‬Stud_ID ‫ والـ‬، composite primary key ‫ دول مع بعض‬Tel ‫ و‬Stud_ID ‫ الـ‬-

. separate attributes within the same table‫ بقسمه لـ‬composite attribute ‫ لو كان عندى‬-

Second Normal Form (2NF)

• A table will be 2NF if:

o The table is 1NF.
o All non-key attributes are fully functional dependent on the primary key (No Partial Dependency).

.1NF ‫ وطبعا تكون عىل الـ‬Partial Dependency ‫ لزم ميكونش فيها‬2NF ‫بتاعن ن يف الـ‬
‫ى‬ relation ‫ عشان أقول أن الـ‬-
‫ الجدول ن‬، partial dependency ‫ فمفيش كدة كدة‬single ‫ بتاعه‬key ‫ اول جدول الـ‬-
non- ‫ لكن معنديش أصال‬composite ‫ بتاعه‬key‫التان الـ‬
‫ن‬
fully ‫ عىل بعضه اذا هو‬composite key‫ معتمد عىل الـ‬G‫اتني لكن بما أن الـ‬ ‫ الجدول التالت ىبق عندى‬، ‫يبق برضو مفيش مشكلة‬
‫ ى‬key attribute
.Subj-Desc ‫ غن الـ‬partial dependency ‫يبق معنديش‬ ‫ ى‬، dependent
 ‫‪ -‬الحل هنا بيكون نان باخد الـ‪ attribute‬دة مع جزء الـ‪ composite key‬الىل بيعتمد عليه واعملهم ن يف جدول لوحدهم ‪ ،‬ساعتها هيكون جزء‬
‫الـ‪ composite key‬هو الـ ‪ primary key‬وبيكون برضو ‪ foreign key‬نف الجدول الىل فات عشان كل الجداول ى‬
‫تبق مربوطة ببعض وألن الـ ‪ G‬كمان‬ ‫ي‬
‫معتمدة عىل الـ‪ composite key‬عىل بعضه فمينفعش افصله‪.‬‬

‫)‪Third Normal Form (3NF‬‬

‫•‬ ‫‪A table will be 3NF if:‬‬

‫‪o The table is 2NF.‬‬
‫‪o There are no transitive dependencies, meaning non-key attributes do not depend on other non-key‬‬
‫‪attributes.‬‬

‫بتاعن ن يف الـ ‪ 3NF‬لزم ميكونش فيها ‪ Transitive Dependency‬وطبعا تكون عىل الـ ‪.2NF‬‬
‫ى‬ ‫‪ -‬عشان أقول أن الـ ‪relation‬‬

‫‪ -‬هنا انا عندى الـ ‪ Level_Mgr‬بيعتمد عىل الـ ‪ level‬نفسه ‪ ،‬والـ ‪ level‬بيعتمد عىل الـ‪ primary key‬الىل هو ‪ Stud_ID‬اذا انا عندى ‪Transitive‬‬
‫‪.dependency‬‬

‫‪ -‬الحل هنا بيكون نان باخد الـ‪ attribute‬دة مع الـ‪ attribute‬الىل بيعتمد عليه واعملهم ن يف جدول لوحدهم ‪ ،‬ساعتها هيكون الـ‪ attribute‬هو الـ‬
‫تبق مربوطة ببعض وألن الـ ‪ level‬معلومة مهمة عن الطالب‬ ‫‪ primary key‬وبيكون برضو ‪ foreign key‬نف الجدول الىل فات عشان كل الجداول ى‬
‫ي‬
‫فالزم يفضل ن يف جدول الطالب‪.‬‬
‫‪ -‬ن يف ‪ 4NF‬و ‪ 5NF‬وهكذا وكل ما بزود كل ما بكرس الـ‪ relations‬وازود عدد الجداول لكن دة مش ‪ recommended‬اوى لن انا كدة بأثر عىل‬
‫ى‬
‫بتاعن‪.‬‬ ‫استخداىم للداتا وعىل حسب الـ‪business case‬‬ ‫الـ‪ perfomance‬بس برضو دة كله بيتحدد عىل طبيعة‬
‫ي‬
 Example:
Given the student sheet example provided, design a relational database schema that captures all the information in
the sheet. The database should be normalized to at least the Third Normal Form (3NF) to eliminate redundancy and
ensure data integrity.

Solution Steps:

1- Turn the report (customer sheet) into 0NF (All attributes in one table):

2- Transform 0NF to 1NF

Problem:

‫يعن بيتكرر قيمهم كتن لكل طالب ن‬

‫وف نفس الوقت األربعة‬ ‫ ن‬multi-valued attributes ‫ الىل نف الخر دول‬attributes ‫عندى هنا األربعة‬ -
‫ي‬
.Repeating Group ‫يعتنوا‬ ‫مرتبطي ببعض فكدة ى‬‫ن‬
‫‪Solution:‬‬

‫‪3- Transform 1NF to 2NF‬‬

‫‪Problem:‬‬

‫ه بس الىل ‪ partially dependent‬ألنها معتمدة بس عىل القسم نفسه ملهاش دعوة برقم الطالب ‪ ،‬لكن‬
‫هنا الـ‪ Dept_desc‬ي‬ ‫‪-‬‬
‫ن‬
‫محتاجي معلومة القسم ومعلومة الطالب‪.‬‬ ‫ن‬
‫التني‬ ‫الـ‪ Ad_Grade‬والـ‪Comments‬‬

‫‪Solution:‬‬
 4- Transform 2NF to 3NF

Problem:

Solution:
ACID Transactions
• A transaction is any operation that is treated as a single unit of work, which either completes fully or does
not complete at all and leaves the storage system in a consistent state.
• The classic example of a transaction is what occurs when you withdraw money from your bank account.
o Either the money has left your bank account, or it has not — there cannot be an in-between state.
• ACID transactions are a set of properties that ensure reliable processing in database management systems.

• They stand for Atomicity, Consistency, Isolation, and Durability:

o Atomicity:
▪ This guarantees that all parts of a transaction are completed successfully.
▪ If any part of the transaction fails, the entire transaction is rolled back to its previous state.
▪ This ensures a transaction is "all or nothing."
o Consistency:
▪ The database must go from one valid state to another, maintaining all predefined rules and
constraints.
▪ After a transaction, all data must meet the integrity constraints, ensuring the database
remains consistent.
o Isolation:
▪ Transactions are often executed concurrently.
▪ Isolation ensures that the intermediate state of a transaction is invisible to other
transactions until it’s completed.
▪ This prevents interference and maintains transaction integrity, as if each transaction were
executed sequentially.
o Durability:
▪ Once a transaction is committed, it is permanently saved, even in the event of a system
failure.
▪ This means any changes made by the transaction are durable and remain in the database.

Example:
-- Start a transaction
START TRANSACTION;

-- Example: Transfer $100 from Account A to Account B

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; -- Deduct from A
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; -- Add to B

-- Commit changes if everything is good

COMMIT;

-- Rollback in case of an error (undo all changes)

ROLLBACK;
About the Author

Connect with me on Linkedin: mohaned-ahmad

Explore more at: GitHub Repository