Database Design Notes PDF

# تفريغ لاب 1 ## How to Design Database - **Analysis** - **Entity** - **Attributes** - **Relationship** - **Entity Relationship Diagram (ER Diagram)** - **Student** - **id** - **name** - **major** - **grade** - **Instructor** - **id**...

# تفريغ لاب 1 ## How to Design Database - **Analysis** - **Entity** - **Attributes** - **Relationship** - **Entity Relationship Diagram (ER Diagram)** - **Student** - **id** - **name** - **major** - **grade** - **Instructor** - **id** - **salary** - **name** - **Department** - **id** - **name** - **Course** - **id** - **name** - **Section** - **id** - **name** - **Relationships** - **Student** **M:M** **Course** - **Course** **1:M** **Section** - **Department** **1:M** **Course** - **Instructor** **M:1** **Department** # تفريغ لاب 2 ## Entity Relationship Diagram - **Entity** - A box with the entity name inside. - **Attributes** - A circle with the attribute name inside. - **Relationship** - A diamond with the relationship name inside. # تفريغ لاب 3 ## Analysis ER Diagram - **Entity** - A box with arrow on the left side, pointing to the right. - **Attribute** - A circle with arrow pointing to the right, then pointing up. - **Relationship** - A diamond with arrow pointing to the right, then pointing up. - **Key Attribute** - Unique and doesn't accept repetition. - For example, id, serial number, SSN. # تفريغ لاب 4 ## Attributes - **Composite Attribute** - Composed of multiple attributes. - For example, address can be composed of city, street, region and building number. - **Multivalue Attribute** - Can have multiple values. - For example, phone number, email, hobbies. - **Complex Attribute** - A more complex version of an attribute. - It is often used to model data that has multiple parts and requires a complex structure. - For example, phone number. - **Derived Attribute** - Derived from other attributes. - For example, age can be derived from the birthdate. - It is not stored in the database but calculated based on other attributes. # تفريغ لاب 5 ## Relationships - **Cardinality Ratio** - 1:1 - 1:M - M:N - **Participation Constraint** - Total - Partial # تفريغ لاب 6 - **Binary Relationship** - A relationship between two entities. - This relationship is represented by a diamond. - **Recursive Relationship** - A relationship between an entity and itself. - Example, employee supervisor. - **Ternary Relationship** - A relationship between three entities. # تفريغ لاب 7 ## Entity Types - **Strong Entity** - A basic entity - **Weak Entity** - Dependent on a strong entity for its existence. - Doesn't have primary key. # تفريغ لاب 8 ## Analysis ER Diagram - **Schema** - The structure of the tables. - Contains columns and relationships between tables. - **Table** - Represents an entity in the database. - Contains data in rows and columns. - **Data** - The content of the table. - Stored in rows and columns. - **Column** - Represents an attribute in the entity. - Stores data for each attribute. - **Entity Name** - Translates to the table name. - **Attribute Name** - Translates to the column name. ## Converting ERD To Relational Schema - **Mapping Algorithm** - How to convert an ERD into a relational schema using a set of rules. # تفريغ لاب 9 ## Relational Schema Mapping - **Primary Key** - A column or set of columns that uniquely identifies each row in a table. - It is used to ensure data integrity and prevent duplicate records. - Choose primary keys that use numbers, as they are easier to handle. - **Composite Attribute** - If the composite attribute includes a single attribute, it is considered a primary key. - If the composite attribute includes more than one attribute, each attribute is considered a candidate key. - **Multi-value Attribute** - Create a new table for each multi-value attribute, adding a foreign key from the original table. - **Derived Attribute** - The derived attribute is not stored in the database, but it is calculated based on other attributes. - Therefore, it is not included in the relational schema. # تفريغ لاب 10 ## DDL And DML - **DBMS** - Database management system - **SQL** - Structured query language - **DDL** - Data definition language - **DML** - Data manipulation language - **DDL Commands** - **create** - Used to create new database objects such as databases, tables, views, and constraints. - **drop** - Used to delete objects from the database. - **alter** - Used to modify the schema of database objects, such as adding or removing columns, changing data types, or adding constraints. # تفريغ لاب 11 ## DDL Commands - **create database** - Used to create a new database. - **drop database** - Used to delete a database. - **create table** - Used to create a new table. - The table name should be chosen carefully to reflect the data it contains. - Each column should have a data type that matches the data it will contain. - Columns can have constraints that define the values they can contain. - **drop table** - Used to delete a table. # تفريغ لاب 12 ## Data Types - **`char(n)`** - Fixed length string - Reserves memory space for a string of specified length. - For example, `char(10)` reserves 10 bytes of memory for a string, even if it contains fewer characters. - **`varchar(n)`** - Variable length string - It reserves space for a string of specified length, but it only uses the actual space needed for the content. - This reduces memory consumption and increases storage efficiency. ## Constraints - **Constraints** - Rules that govern the data in a table. - Help to ensure data integrity and enforce business rules - **Types of Constraints** - **Not Null** - Ensures a column cannot contain null values. - **Primary Key** - Uniquely identifies each row in a table. - Each table can only have one primary key. - **Unique** - Ensures that a column cannot contain duplicate values. - **Foreign Key** - Creates a relationship between two tables. - It is used to link data in one table to another table. - It refers to a primary key in another table. - **Default** - Specifies a default value for a column. - **Check** - Ensures that the data in a column meets specific criteria. # تفريغ لاب 13 ## Constraints: Not Null Constraint - **Not Null Constraint** - Specifies that a column cannot contain NULL values. - This ensures that the column always has a value, which can be useful for maintaining data integrity and enforcing business rules. - **Example** - `balance float not null` - This code specifies that the `balance` column in the `account` table cannot contain a NULL value. - The `not null` constraint is helpful for ensuring that all accounts have a balance value, even if it is 0. # تفريغ لاب 14 ## Constraints: Primary Key Constraint - **Primary Key Constraint** - Used to identify a unique row in a table - It is created to maintain data integrity and enforce business rules. - A table can only have one primary key constraint. - It is created using the `primary key` keyword. # تفريغ لاب 15 ## Constraints: Foreign Key Constraint - **Foreign Key Constraint** - Specifies that data entered in a column must match an existing value in another table. - The `references` keyword defines the primary key column in another table to which the current column is linked. - It is necessary to ensure data integrity and relationships between tables. # تفريغ لاب 16 ## Constraints: Unique Constraint - **Unique Constraint** - Ensures that all values in a column are distinct and unique. - This helps prevent duplicate data and maintain data integrity - - The `unique` keyword defines a unique constraint. - **Using Unique Constraints** - It is important to carefully consider the unique constraints you create for a table. - Use unique constraints for attributes that absolutely need to be unique, like customer ID or product codes, instead of using them for descriptive attributes like name. # تفريغ لاب 17 ## Constraints: Check Constraint - **Check Constraint** - Enforces a specific rule or condition on the data that can be stored in a column. - The `check` keyword defines a check constraint. - This helps to maintain data integrity and enforce any business rules related to the data. - It is important to ensure that the `check` condition you specify evaluates to true for all data, or constraint will fail. # تفريغ لاب 18 ## Constraints: Default Constraint - **Default Constraint** - Automatically assigns a default value to a column if no value is provided during data entry. - This constraint helps simplify data entry and ensure consistency in the data. - It is defined using the `default` keyword. - **Example** - `city varchar(10) constraint city_default default 'Irbid'` - This code specifies that the `city` column in the `student` table will have a default value of `Irbid` if no city is provided during data entry. # تفريغ لاب 19 ## Domain Values & Constraints - **Domain Values** - A set of possible values for a column. - It is important to define domain values for columns to ensure data consistency and maintain data integrity. - **Example:** - `A/child` and `B/parent` - `A/child` has domain values of `A1` and `A2`. `B/parent` has domain values of `B1` and `B2`. - **Null Values** - A special domain value that represents the absence of a value for a column. - The primary key column in the table cannot accept a null value. - On the other hand, the foreign key column in the table can accept a null value. - **Understanding Referencing & Constraints** - The **referencing** keyword in a constraint is used to establish a link between two tables. - It ensures the data in a column matches an existing value in the referenced table. - **Example:** - `constraint fk_student_department foreign key (department_id) references department (department_id)` - It ensures that the values in the `department_id` column in the `student` table match existing values in the `department_id` column in the `department` table. # تفريغ لاب 20 ## Alter Table Command - **`ALTER TABLE` Command** - Used to modify the structure of an existing table. - This command is very flexible and can be used to add, remove, or modify columns, constraints, and other aspects of a table’s schema. - It is also used to rename a table, change its data type or add constraints. ## `ALTER TABLE` Command: Adding Columns - This is done by using the `add column` clause followed by the column name, data type, and any necessary constraint. - **Example:** - `ALTER TABLE person ADD COLUMN phone varchar(10) NOT NULL` - This command adds a `phone` column with a `varchar(10)` data type to the `person` table and sets this column as `NOT NULL`. ## `ALTER TABLE` Command: Dropping Columns - This is done using the `drop` keyword followed by the `column` keyword and the column name - **Example:** - `ALTER TABLE person DROP COLUMN city` - This command removes the `city` column from the `person` table. ## `ALTER TABLE` Command: Changing Data Types - This is done by using the `ALTER TABLE` command followed by the `ALTER COLUMN` command and specifying the column name and new data type. - **Example:** - `ALTER TABLE person ALTER COLUMN phone varchar(20)` - This command changes the data type of the `phone` column in the `person` table from `varchar(10)` to `varchar(20)`. # تفريغ لاب 21 ## Adding Constraints To A Table - **Adding Constraints** - Adding constraints to a table after it has been created involves using the `ALTER TABLE` command. - **Steps** - **1. Specify the table**. - Example: `ALTER TABLE person ...` - **2. Specify the constraint action**. - Example: `ADD CONSTRAINT ...` - **3. Define the constraint name**. - Example: `ADD CONSTRAINT phone_unique ... ` - **4. Define the constraint type**. - Example: `UNIQUE ...` - **5. Specify the column to be constrained**. - Example: `UNIQUE (phone)` - **Example Code:** - `ALTER TABLE person ADD CONSTRAINT phone_unique UNIQUE(phone)` - This command adds a unique constraint named `phone_unique` to the `phone` column in the `person` table. # تفريغ لاب 22 ## Dropping Constraints - **Dropping Constraints** - This is done using the `ALTER TABLE` command and the `DROP CONSTRAINT` clause. - **Steps** - **1. Specify the table**. - Example: `ALTER TABLE person ...` - **2. Specify the constraint action**. - Example: `DROP CONSTRAINT ...` - **3. Specify the constraint name**. - Example: `DROP CONSTRAINT city_default` - **Example Code:** - `ALTER TABLE person DROP CONSTRAINT city_default` - This command drops the constraint named `city_default` from the `person` table. # تفريغ لاب 23 ## DML Commands - DML commands are used to manipulate data within tables. - They allow you to add, update, delete, and retrieve data from a database. - The DML commands are: - **insert** - Adds data to a table. - **delete** - Removes data from a table. - **update** - Modifies existing data within a table. - **select** - Retrieves data from a table - It is a very powerful command used in fetching data, filtering data based on certain conditions, and performing various operations like sorting, grouping, and calculating values. # تفريغ لاب 24 ## Insert Command - **`INSERT` Command** - Used to add new rows (records) of data to a table. - **Syntax** - `INSERT INTO table_name (column1, column2, ...)` - `VALUES (value1, value2, ...);` - **Example:** - `INSERT INTO person (ssn, fname, city, salary) VALUES ('234', 'Ali', 'Irbid', '450');` - This inserts a new row into the `person` table with the following values: `ssn = '234', fname = 'Ali', city = 'Irbid'`, and `salary = '450'`. # تفريغ لاب 25 ## Select Command - **`SELECT` Command** - Retrieves data from a database. - It is a very powerful command, and you can use selection criteria and various clauses to retrieve data as per your requirements. - **Syntax:** - `SELECT column1, column2, ... FROM table_name WHERE condition;` - **Example 1:** - `SELECT * FROM person;` - This command retrieves all columns and rows from the `person` table. - **Example 2:** - `SELECT fname, city, salary FROM person WHERE city = 'Irbid';` - This command retrieves the `fname`, `city`, and `salary` columns from the `person` table where the city is `'Irbid'`. # تفريغ لاب 26 ## Select Command: Filtering Data - **`DISTINCT` Keyword** - Used to retrieve unique values from a column. - **Example:** - `SELECT DISTINCT fname, city FROM person;` - This command retrieves the unique values of `fname` and `city` from the `person` table, eliminating any duplicate records. - **`AS` Keyword** - Used to assign an alias name to a column or table. - **Example:** - `SELECT fname + ' ' + lname AS full_name, GPA FROM student;` - This command combines the `fname` and `lname` columns with a space in between and assigns the name `full_name` to the resulting column. The `GPA` column is also retrieved. - **Arithmetic Operations** - You can perform mathematical operations in the `SELECT` statement to calculate new values. - **Example:** - `SELECT fname, mark + 3 AS mark_with_increase FROM student;` - This command retrieves the `fname` and `mark` columns from the `student` table. - It also adds 3 to each `mark` value and assigns the result to a new column named `mark_with_increase`. # تفريغ لاب 27 ## Select Command: Filtering Data: Comparing Values - **`WHERE` Clause** - Used to filter the data based on conditions. - **Example:** - `SELECT * FROM student WHERE GPA >= 84;` - This command retrieves all rows from the `student` table where the `GPA` is greater than or equal to 84. ## Select Command: Filtering Data: Using Range Operators - **`BETWEEN` Operator** - Used to find values within a specified range. - The `BETWEEN` operator checks whether a value is within a specified range (inclusive of both endpoints). - **Example:** - `SELECT * FROM student WHERE GPA BETWEEN 70 AND 75;` - This command retrieves all rows from the `student` table where the `GPA` is between 70 and 75, inclusive. - **`NOT BETWEEN` Operator** - Retrieves data that is not within a specified range. - **Example:** - `SELECT * FROM student WHERE GPA NOT BETWEEN 70 AND 75;` - This command retrieves all rows from the `student` table where the `GPA` is not between 70 and 75. # تفريغ لاب 28 ## Select Command: Filtering Data: Using `IN` Operator - **`IN` Operator** - Specifies a set of values to search for and retrieves all rows where the column value matches any of the values in that set. - **Example:** - `SELECT * FROM student WHERE GPA IN (90, 85, 75, 60);` - This command retrieves all rows from the `student` table where the `GPA` value is either 90, 85, 75, or 60. # تفريغ لاب 29 ## Select Command: Sorting Data - **`ORDER BY` Clause** - Used to sort the retrieved data in the `SELECT` statement. - By default, the data is sorted in ascending order. - **Syntax** - `SELECT column1, column2, ... FROM table_name ORDER BY column_name ASC/DESC;` - **Example:** - `SELECT fname, mark FROM student_DB ORDER BY mark DESC;` - This command retrieves the `fname` and `mark` columns from the `student_DB` table and sorts the data in descending order based on the `mark` column. - **`TOP` Keyword** - Used to retrieve a specific number of rows. - **Example:** - `SELECT TOP 2 fname, mark FROM student_DB ORDER BY mark DESC;` - This command retrieves the top 2 rows from the `student_DB` table based on the `mark` column in descending order. # تفريغ لاب 30 ## Aggregate Functions - **Aggregate Functions** - Used to perform calculations on a set of values. - **Example:** - `SELECT AVG(mark) AS avg_mark, MAX(mark) AS max_mark FROM student_DB;` - This command calculates the average and maximum marks from the `student_DB` table and displays them as `avg_mark` and `max_mark` respectively. # تفريغ لاب 31 ## Aggregate Functions (Continued) - **Aggregate Functions** - **`COUNT()`** - Counts the number of rows in a table or the number of non-null values in a column. - **`SUM()`** - Calculates the sum of all values in a column. - **`MIN()`** - Finds the minimum value in a column. - **`MAX()`** - Finds the maximum value in a column. - **`AVG()`** - Calculates the average of all values in a column. # تفريغ لاب 32 ## `GROUP BY` Clause - **`GROUP BY` Clause** - Used to group rows that have the same value in a specified column. - After grouping, you can apply aggregate functions to calculate summaries for each group. - **Syntax** - `SELECT column1, column2, ... FROM table_name GROUP BY column_name;` - **Example:** - `SELECT major, AVG(mark) AS avg_mark FROM student_DB GROUP BY major;` - This command retrieves the `major` and `avg_mark` columns from the `student_DB` table and groups the rows by the `major` column. The `AVG` function then calculates the average mark for students in each major. - **Combining `GROUP BY` With Aggregate Functions** - You can often combine the `GROUP BY` clause with aggregate functions to calculate statistics specific to each group. - For example: - `SELECT major, COUNT(*) AS num_students, AVG(mark) AS avg_mark FROM student_DB GROUP BY major;` - This command retrieves the `major`, `num_students`, and `avg_mark` columns from the `student_DB` table. It groups the rows by the `major` column and then calculates the count and average mark for each group. - This allows you to efficiently analyze data and gain insights about different categories or groups within your data. You can see the information you requested is very extensive. Please let me know if you want to further explore any of the topics.