Database Design Fundamentals

Study Notes

Database Design Fundamentals

Database Design is a process, not a single task
DML (Data Manipulation Language): commands for managing data in a database
DML used for inserting, updating, deleting, retrieving data
DML used by developers and database administrators to manage data
DML often used in combination with DDL
DDL (Data Definition Language) defines and modifies the structure of database objects

DML Commands

SELECT: Used to retrieve data from one or more tables
INSERT: Used to add new rows of data to a table
UPDATE: Used to modify existing rows of data in a table
DELETE: Used to remove one or more rows of data from a table

INSERT Command Syntax

Insert a row: INSERT INTO table_name (col1, col2, ...) VALUES ("value1", "value2", ...); Example: INSERT INTO Person (id, LastName, City) VALUES (222, 'Hassan', 'Assuit');
Insert multiple rows: INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), ... (value_list_n);

SQL Server Processing Order

SQL Server processes the FROM clause first, then the SELECT clause.

SQL Comparison and Other Conditions

Comparison: = (equal), > (greater than), >= (greater than or equal), < (less than), <= (less than or equal), <> or != (not equal)
Other: BETWEEN (inclusive range), IN (match any value from a list), LIKE (match a pattern), IS NULL (is a null value), IS NOT NULL (is not a null value)

Logical Conditions (Precedence)

Logical: AND, OR, NOT
Precedence Rules: Operations are performed in order, prioritizing Arithmetic Operators, followed by Concatenation, then Comparisons, then NOT, IN, BETWEEN, then logical conditions like AND and OR.

Predicates and Operators

Common Predicates: IN, BETWEEN, LIKE
Logical Operators: AND, OR, NOT
Comparison Operators: =, >, >=, <, <=, <>, !=
Arithmetic Operators: +, -, *, /, %
Concatenation: +

Example Queries (Student Data)

Query to retrieve all platforms where students haven't graduated:

SELECT name FROM Platform WHERE graduate = 1;

Query to retrieve students who have a grade of 'A' and attendance greater than or equal to 15:

SELECT appno, pfname, Foundgrade, attd FROM Students_platform WHERE Foundgrade = 'A' AND attd >= 15;

Query to retrieve students from Science or Engineering Faculty:

SELECT S.appno, S.name, F.faculty FROM Students S JOIN Fac_majors F ON S.f_code = F.f_code WHERE F.faculty = 'Science' OR F.faculty = 'Engineering';

Database Table Assignment/Schema

The assignment describes creating and populating a "CompanyDB" database with several tables including Employee, Department, Works_for, Project, Dependent.
Specific data is also included within data tables for sample values (e.g., employee names, address, salary etc).

Query examples/instructions

Query to display project information starting with the letter 'a'
Query to list employees in department 30 earning between 1000-2000 in salary
Query to display employees working in department 10
Query to display female employees under a supervisor whose id is 223344
Query to show the first and last names along with supervisor's id for employees.