DataBase
Page 1
DATA BASE
Eng. Marwa Ghazy
Page 2
Course Topics
Database Management Systems Introduction
Database Architecture
Properties of Database Transactions (ACID)
Different Types of Database Management System(DBMS)
Who Deals with Database
Normalization
Entity-Relationship Model
SQL- The Relational Database Standard Language
Page 3
INTRODUCTION
Page 4
Page 5
DATABASE ARCHITECTURE
Page 6
Page 7
PROPERTIES OF DATABASE (ACID)
Page 8
Page 9
DIFFERENT TYPES OF DBMS
Page 10
Relational vs Non-Relational
Relational Database:
Collection of data items with predefined relationships.
Organized as tables (rows, columns)
Uses SQL for communication.
Non-Relational Database (NoSQL):
Does not use tabular schema.
Data stored as key/value pairs or JSON documents.
Page 11
Types of Non-Relational Databases
Can use different query languages than SQL:
Key/value data stores
Columnar data stores
Document data stores
Graph data stores
Types of non-relational database: NoSQL (not only SQL)
Page 12
Key/Value Data Stores
Stores data as key-value pairs.
Key serves as unique identifier.
Example databases: Riak, Redis, Memcached, etc.
Page 13
Column Data Stores
Data organized into columns on disk.
Example with IoT sensors data.
Databases: Apache Cassandra, Maria, etc.
Page 14
Document Data Stores
Manages more complex data structures than key-value stores.
Data stored as documents (XML, JSON).
Examples: Amazon DynamoDB, MongoDB, etc.
Page 15
Graph Data Stores
Based on Graph Theory.
Basic building blocks: vertices and edges.
Examples: Neo4j, JanusGraph, etc.
Page 16
Relational vs Non-Relational
Relational:
Best for structured transactional data.
Predefined schema with specific entities and relations.
Non-Relational:
Best for unstructured data
Flexible, more scalable.
Page 17
Scaling Up vs Scaling Out
Scaling Up: Adds resources to physical servers.
Scaling Out: Adds more servers to spread workload.
Page 18
Data Warehousing
Repository providing integrated data for management support.
Subject-oriented, recorded over time, storing summaries.
Integrates multiple data sources.
Page 19
Data Warehouse
Page 20
Star Schema
DimProduct, FactResellerSales, DimDate etc.
Page 21
Data Warehouse Key Features
Subject-oriented, integrated, time-variant, non-volatility.
Page 22
Big Data
Term for data sets too large or complex for traditional databases.
Three V's: Volume, Velocity, Variety.
Types of data: Unstructured, Semi-structured, Structured.
Page 23
WHO DEALS WITH DATABASE?
Page 24
Database Users
Roles: DBA, System Analysts, Database Designer, Developer, BI Developer, Application Programmers, End Users.
Page 25
NORMALIZATION
Page 26
Normalization Process
Removes redundant data.
Benefits: faster sorting, fewer NULLs, compactness.
Disadvantages: more joins needed, slower retrieval.
Page 27
Normal Forms
First Normal Form: Eliminates repeating groups.
Second Normal Form: Separate tables for sets of values.
Third Normal Form: Eliminates fields that do not depend on the key.
Page 28
ENTITY-RELATIONSHIP MODEL
Page 29
Database Life Cycle
Analysis
Design (ERD Diagram)
Mapping
Implementation
Testing
Maintenance
Page 30
Entity-Relationship Diagram (ERD)
Displays relevant entities and relationships required by a business.
Page 31
Basic Constructs of the E-R Model
Entities
Attributes
Relationships
Page 32
ERD Components
Rectangles, Diamonds, Ellipses for entities, relationships, and attributes respectively.
Page 33
Strong vs Weak Entities
Strong Entity: Has a primary key.
Weak Entity: Lacks sufficient attributes for primary key.
Page 34
Types of Attributes
Simple, Composite, Multi-valued, Derived, Complex.
Page 35
Simple Attribute Examples
Access-date, cust-name, etc.
Page 36
Composite Attributes
First, Last names, date of birth examples.
Page 37
Derived Attributes
Age derived from birth date.
Page 38
Multi-valued Attributes
Examples: Phone numbers, addresses.
Page 39
Complex Attributes
Street, city, country addresses.
Page 40
Relationships
Association among entities, may have attributes.
Page 41
Properties of Relations
Degree, Cardinality, and Participation Constraints.
Page 42
Degree of Relationships
Unary, Binary, Ternary relationships.
Page 43
Recursive Relationship (Unary)
Same entity participates multiple times.
Page 44
Binary Relationship
Degree of 2, example of employee with branch.
Page 45
Ternary Relationship
Degree of 3, examples involving products, vendors, and warehouses.
Page 46
Cardinality
One-One, One-Many, Many-Many relationships.
Page 47
Mapping Cardinalities
Examples of relationships with entities like Employees and Cars.
Page 48
Participation Constraints
Total and Partial participation examples.
Page 49
Example Constraints
Employee and car assignments.
Page 50
Example of Department Hiring Employees
Department controls number of employees.
Page 51
Types of Keys
Candidate Key, Primary Key, Foreign Key, Composite Key, etc.
Page 52
Candidate Key
Uniquely identifies an entity, example: SSN.
Page 53
Primary Key
Chosen candidate key that uniquely identifies an entity.
Page 54
Example of Primary Key
Underlined notation in ER diagrams.
Page 55
ER Diagram Notation
Weak relationships represented as double line diamonds.
Page 56
Case Study Overview
Structure of a company with employees, departments, and projects.
Page 57
Detailed Case Study Components
Attributes for employees, departments, and projects recorded.
Page 58
Definitions
Table, Attribute, Row, Database terms explained.
Page 59
ER-to-Relational Mapping
Page 60
Mapping Procedure
Different methods for handling various relationships in ER modeling.
Page 61
Detailed Mapping of Components
Page 62
Mapping Result Overview
Page 63
STANDARD QUERY LANGUAGE(SQL)
Page 64
SQL SERVER
Management tools consist of Desktop app and service.
Page 65
SQL Categories
DDL, DML, DCL, DQL, TCL: Definitions and statements for each category.
Page 66
SQL Categories (Cont.)
Additional explanations for DCL, DQL, and TCL.