Example 2:

Below are some queries about create table, drop or alter it, insert data into table, etc. Please read each part carefully then write and execute it in MS SQL server management studio.

/* The following lines will help you debug your code as you write and run it. DROP TABLE Student; DROP TABLE Dependants; DROP TABLE Faculty; 

DROP DATABASE YourName; */ 

CREATE DATABASE YourName; /* enter your name here for the database name */ GO 

USE YourName /* open your database */ 

/* Create the table and the columns. The Entity becomes the table name and the attributes become the column. The table is contrained by two not null contrains on column name and a Primary Key which by default cannot be null. I use IMAGE to show that pictures can be used in databases also. 

*/

 CREATE TABLE Student ( 

StudentID StuFirstname StuLastname StuAddress StuCity StuState StuZIP 

);

int IDENTITY(1000,1) PRIMARY KEY, VARCHAR (30) NOT NULL, VARCHAR (30) NOT NULL, VARCHAR (100), 

VARCHAR (30), VARCHAR (2), VARCHAR (12), 

 /* In the faculty table below we use a money variable type for convienience. 

 This is for US currency. It would be possible to use a decimal double or number and 

 contrain it to two decimal places. */ CREATE TABLE Faculty ( 

FacultyID FacFirstname FacLastname FacAddress FacCity FacState FacZIP FacSalary FacHireDate ); 

int IDENTITY(1000,1) PRIMARY KEY, VARCHAR (30) NOT NULL, VARCHAR (30) NOT NULL, VARCHAR (100), 

VARCHAR (30), VARCHAR (2), VARCHAR (12), MONEY, /* This allows for US currency to be entered */ DATE NOT NULL DEFAULT GETDATE(), 

/* The table below is for the employee dependants. The dependants are a weak entity to the faculty member. Deleteing a faculty member without deleting the dependant will create orphaned data rows. */ 

CREATE TABLE Dependants( 

DependantID DepFirstname DepLastname FacultyID 

);

 int IDENTITY(1000,1) PRIMARY KEY, VARCHAR (30) NOT NULL, VARCHAR (30) NOT NULL, int NOT NULL 

 /* The faculty to dependant is a one to many relationship so 

 the FacultyID primary key is added to the dependant table to create the foriegn key*/ 

 /* Deleteing a faculty member without deleting the dependant will create orphaned data rows. So we create a foreign key to build a relation between the faculty table and the dependant table. Adding "ON DELETE CASCADE" to the reference will cause the data that relates to the faculty dependant in the dependant table to be automatically be deleted if the faculty member is 

deleted. This solves the problem with the orphaned data. */ 

 ALTER TABLE Dependants ADD CONSTRAINT FK_FacultyID FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID) ON DELETE CASCADE; 

 /* Now we add the data into the Student table using the insert SQL command */ 

 INSERT INTO Student (StuFirstname,StuLastname,StuState) VALUES ('John', 'Martin','Ga'); 

 INSERT INTO Student (StuFirstname,StuLastname,StuState) VALUES ('John','Wayne','Fl'); 

 INSERT INTO Student (StuFirstname,StuLastname,StuState) VALUES ('Jim' , 'Best','Ga'); 

 /* Now we add the data into the Faculty table using the insert SQL command */ 

 INSERT INTO Faculty(FacFirstname,FacLastname,FacSalary) VALUES ('John','Doe', 65000.00); 

 INSERT INTO Faculty(FacFirstname,FacLastname,FacSalary) VALUES ('Ron','Gant', 72560.00); 

 INSERT INTO Faculty(FacFirstname,FacLastname,FacSalary) VALUES ('Andrea','Hartman', 89587.25); 

 INSERT INTO Faculty(FacFirstname,FacLastname,FacSalary) VALUES ('Paul','Smith', 68723.00); 

 /*Now we add the data into the Dependants table using the insert SQL command */ 

 INSERT INTO Dependants (DepFirstname,DepLastname,FacultyID) VALUES ('Rhonda','Gant','1001'); 

 INSERT INTO Dependants (DepFirstname,DepLastname,FacultyID) VALUES ('Phil' ,'Hartman','1002'); 

 /* Using the "SELECT * FROM Faculty;" Statement will use the star wildcard to select all rows within the table and will output them to the user. */ SELECT * FROM Faculty; 

 /* You can also list the columns and table you want to get the data prom. */ 

 SELECT StuFirstname, StuLastname, StuState FROM Student; 

/* The "where" clause allows the user to be selective and base the query on a value 

 or range of values */ 

 SELECT * FROM Faculty WHERE FacSalary > '70000'; 

 /* Next we show how the "DELETE ON CASCADE" works */ 

 SELECT * FROM Faculty; SELECT * FROM Dependants; 

 Delete FROM Dependants WHERE DepLastname = 'Hartman'; Delete FROM Faculty WHERE FacLastname = 'Gant'; 

 SELECT * FROM Faculty; SELECT * FROM Dependants;