Scribd
Upload
4 views14 pages

Week03 Detailed SQL Server Lecture

This document covers key concepts in SQL Server database design, including primary and foreign keys, indexes, and relationships. It outlines good schema design rules, functional dependencies, and normalization to 3NF. Additionally, it includes lab tasks for normalizing datasets, creating ER diagrams, and writing SQL scripts.

Uploaded by

temoor.arshad3432
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views14 pages

Week03 Detailed SQL Server Lecture

This document covers key concepts in SQL Server database design, including primary and foreign keys, indexes, and relationships. It outlines good schema design rules, functional dependencies, and normalization to 3NF. Additionally, it includes lab tasks for normalizing datasets, creating ER diagrams, and writing SQL scripts.

Uploaded by

temoor.arshad3432
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Week 03 – SQL Server Database

Design
• Primary & Foreign Keys, Indexes
• Relationships
• Schema Design
• Functional Dependencies
Primary Key (PK)
• Uniquely identifies each record
• No NULL values
• Only one PK per table
• Example:
• StudentID INT PRIMARY KEY
Foreign Key (FK)
• Links two tables
• References a Primary Key
• Ensures referential integrity
• Example:
• FOREIGN KEY (DeptID) REFERENCES
Department(DeptID)
Indexes
• Improve SELECT query performance
• Types: Clustered, Non-Clustered
• Example:
• CREATE INDEX idx_name ON Student(Name)
One-to-One Relationship
• One record relates to only one record in
another table
• Example: Person – Passport
One-to-Many Relationship
• One record relates to multiple records
• Example: Department – Employees
Many-to-Many Relationship
• Many records relate to many records
• Resolved using a junction table
• Example: Students – Courses
Good Schema Design Rules
• Avoid redundancy
• Use proper data types
• Apply constraints
• Use normalization
Functional Dependency (FD)
• A → B means A determines B
• Example: StudentID → Name
Types of Dependencies
• Full Dependency
• Partial Dependency
• Transitive Dependency
Data Anomalies
• Insertion Anomaly
• Update Anomaly
• Deletion Anomaly
Normalization to 3NF
• 1NF: Atomic values
• 2NF: No partial dependency
• 3NF: No transitive dependency
SQL Example – PK & FK
• CREATE TABLE Department (DeptID INT
PRIMARY KEY, DeptName VARCHAR(50));
• CREATE TABLE Employee (EmpID INT PRIMARY
KEY, DeptID INT,
• FOREIGN KEY (DeptID) REFERENCES
Department(DeptID));
Week 03 Lab Tasks
• 1. Normalize dataset up to 3NF
• 2. Create ER Diagram
• 3. Explain relationships
• 4. Write SQL scripts

You might also like