SQL Database Design Tutorial

SQL is the programming language used for creating and managing a database.

SQL (Structured Query Language) is the standard programming language used for creating and managing a database, a collection of related data stored in tables. You can form relations between tables with primary and foreign keys. A primary key uniquely identifies a record in a table. A foreign key in a child table represents the primary key of the parent table, and links them. For example, your office has a database with "customer," "orders" and "employees" tables since a customer places orders which are processed by employees. The "customer_ID" is the primary key which identifies each customer record in the table. The "orders" table will consist of this key as a foreign key to link the customers with their orders.

Instructions

1. Identify the entities for your database. For example, the entities for an office database are "customer," "orders," "employees" and "departments."

2. Determine attributes for every entity. For example, the customer table will include ID, "name," "address," "zip code," "phone," "state" and "country."

3. Establish relationships between the tables. For example, "customer" places "orders" and "orders" are processed by "employees."

4. Define data types and constraints for each of the attributes. For example, the "customer_ID" can be a numeric value with maximum of five digits whereas "name" will be an alphanumeric value consisting of the letters, numbers and special characters. Note that the numbers are treated here as text and you cannot perform mathematical operations on them.

5. Create an entity relationship diagram to make the relationships clear. You can have one-to-many, many-to-many, one-to-one. A one-to-many relationship exists where one attribute can have multiple values like phone number. One customer can have more than one phone number. A many-to-many relationship is depicted when each attribute can have multiple values. For instance, an order consists of many items and one item can be a part of many orders. A one-to-one relationship exists when each attribute can have only one value.

6. Create the database by using the "Create Database" command in SQL. For example, you can create the office database using the following command:

   CREATE TABLE customer

   (Customer_ID NUMBER(5) PRIMARY KEY,

   Name VARCHAR2(50) NOT NULL,

   Street VARCHAR2(50),

   City VARCHAR2(25),

   State VARCHAR2(20),

   Zip_Code VARCHAR2(10));

   The above command will create a table with all the attributes with specific data types, constraints and a primary key.

7. Similarly create other tables and establish relationships. For examples, Customer_ID is a foreign key for the "order" table. You can use the following syntax for adding foreign keys in the table:

   CREATE TABLE orders

   (Order_Number NUMBER(5) PRIMARY KEY,

   Name VARCHAR2(50),

   Customer_ID NUMBER(5),

   CONSTRAINT order_customer_ID_fk

   FOREIGN KEY(Customer_ID)

   REFERENCES customer(Customer_ID));

   The above command creates the Order table and also identifies the relationship between the customer and orders table. you have to create a column for the foreign key also.

8. Populate the database by using the following SQL syntax:

   INSERT INTO CUSTOMER

   VALUES(00001,'BUZZWORD TECHNOLOGIES','2200 WATERSIDE PARKWAY','DALLAS','TEXAS', '75080');

   Each value corresponds to an attribute in the database. The order of values should be same as the attributes in the table creation. Use this syntax to enter individual records one at a time. Note that alphanumeric values are enclosed in single quotes.

9. Extract information from the database by using "SELECT" statements. For example, to view all the records in the customer table, enter the following syntax:

   SELECT * FROM customer;

   This will show all the records in the customer table.

10. Restrict the rows retrieved from the table by adding "WHERE" clause, which selects rows that satisfies criteria in the clause. For example, you want to see all the customers residing in California. Modify the above SELECT statement in the following way:

    SELECT * FROM customer WHERE State = "CA";

    You can see all the customers who reside in California by executing this command.

11. Update a table using the ALTER command. You can use this command to change any attributes values or add columns. For example, You want to add a column to include the status of membership of a customer. Enter the following SQL syntax:

    ALTER TABLE customer ADD status VARCHAR(5);

    Executing the command will add a new column to the existing customer table.

12. Delete the attributes, tables and rows by using the "update" and "drop" commands in the following way:

    DROP TABLE customer CASCADE CONSTRAINTS;

    This command will delete the customer table. The "CASCADE CONSTRAINTS" will ensure that the customer table will be dropped even if there are records in the "orders" table.

    You can use the "update" command in the following way:

    UPDATE customer

    SET name = "Whalen"

    WHERE customer_id = 00001;

    This command will update the customer table and replace the existing name of customer to "Whalen" where customer_id is 00001.

Tips & Warnings

• You can add the constraints after creating the table also by using the "ALTER TABLE table name" command.

Resources

• SQL for Beginners: Part3 -- Database Relationships| Nettuts+
• Stanford University: Getting Started with Oracle
• Microsoft: Designing Tables