Data modeling is the process of defining the structure and relationships of data within a database. It helps ensure that data is organized efficiently and accurately, allowing for easy retrieval and manipulation. Data modeling typically involves creating entity-relationship diagrams (ERDs) and defining tables, columns, and relationships.
Notes
1. Constraints (Primary Key, Unique, Check)
Constraints are rules that you can apply to columns in a SQL Server table to enforce data integrity and validity.
a) Primary Key Constraint:
● Primary Key ensures that each row in a table is uniquely identified.
● It prevents duplicate values in the specified column(s).
● Typically used for columns like "CustomerID" or "ProductID."
Example :
Example of defining a primary key constraint
CREATE
TABLE Students (
StudentID INTPRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
b) Unique Constraint:
● Unique Constraint ensures that the values in the specified column(s) are unique
● It allows NULL values but prevents duplicates.
● Useful for columns that should have unique values but can contain NULLs.
Example :
Example of defining a unique constraint.
CREATE TABLE
TABLE Employees (
EmployeeID INTUNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50),
c) Check Constraint:
● Check Constraint enforces specific conditions on the values allowed in a column.
● It can be used to ensure that values meet certain criteria
● For example, you can use it to restrict dates to a certain range or ensure that prices are positive.
Example :
Example of defining a check constraint
CREATE TABLE
Products (
ProductID INTUNIQUE,
ProductName VARCHAR(50),
Price DECIMAL(10, 2) CHECK (Price >= 0) );
2. Common Data Types
SQL Server supports a variety of data types to store different types of data efficiently. Some common data types include:
● INT : Used for whole numbers like IDs and counts.
●VARCHAR : Stores variable-length character strings (e.g., names, descriptions).
● DECIMAL: Handles fixed-point numbers with specified precision and scale (e.g., currency values).
● DATE :Stores date values (e.g., birthdates, order dates).
● BIT :Represents boolean values (1 for true, 0 for false).
Example :
Creating a New Table
CREATE TABLE
Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Birthdate DATE,
Salary DECIMAL(10, 2));
IsActive BIT);
3. Working with Table Columns
a) Adding Columns:
You can add columns to an existing table using the ALTER TABLE statement:
Example :
Adding a new column to an existing table
ALTER TABLE
Customers
ADD
PhoneNumber
VARCHAR(20);
b) Modifying Columns:
You can modify the data type or other properties of an existing column:
Example :
Modifying the data type of a column
ALTER TABLE
Products
ALTER COLUMN
Price
DECIMAL(12, 2);
c) Dropping Columns:
To remove a column from a table, use the ALTER TABLE statement with the DROP COLUMN clause:
Example :
Dropping a column from a table
ALTER TABLE
Employees
DROP COLUMN
Birthdate;
4. Real-world Examples of Constraints and Data Types
Online Banking System:
● Constraints: Primary Key ensures unique account IDs. Unique constraints on usernames and email addresses prevent duplicates. Check constraints ensure that withdrawal amounts are positive.
● Data Types: INT for account IDs, VARCHAR for names, DECIMAL for account balances, DATE for transaction dates, and BIT for account status (active or inactive).
5. Key Takeaways
● Constraints (Primary Key, Unique, Check) enforce data integrity rules in SQL Server tables
● Common data types (INT, VARCHAR, DECIMAL, DATE, BIT) are used to define the type of data a column can store.
● You can add, modify, or drop columns in an existing table using SQL Server's ALTER TABLE statement.
6. Exercise Questions
1. Create a table named "Books" with columns for book ID (INT), title (VARCHAR), publication date (DATE), and price (DECIMAL). Define appropriate constraints.
2. Explain the differences between a Primary Key and a Unique constraint. Provide scenarios where you would use one over the other.
3. Create a table named "Employees" with columns for employee ID (INT), first name (VARCHAR), last name (VARCHAR), and date of hire (DATE). Add a Check constraint to ensure that the date of hire is not in the future.
4. Suppose you have a table for customer addresses. Add a column to the table to store the city (VARCHAR). Update the table to set a default city for existing records.
5. Design a table structure for a product catalog with columns for product ID (INT), product name (VARCHAR), category (VARCHAR), and price (DECIMAL). Define a Unique constraint to ensure that product names are unique within a category.
6. Discuss the advantages and disadvantages of using a Check constraint to enforce data validation rules in a database.
Data Definition Language (DDL) statements in SQL Server are used for defining and managing database objects, including tables. Here's how you create tables: