Writing SQL Statements

Running head : SQL

Writing SQL Statements 3 .7

CREATE TABLE PET_OWNER

OwnerID Numeric (10 ) Primary Key

Name Varchar (20

Phone Numeric (10

Email Varchar (50

p OwnerID - is taken as a numeric data field and also marks as a primary key

Name - is taken to be Varchar data type

Phone - The Phone number is again taken as a 10 digit numeric data field

Email - It is taken as Varchar with size 50 as it can be alphanumeric in nature

3 .8

CREATE TABLE PET p

PetID Numeric (10 ) Primary Key

Name Varchar (20

Type Varchar (10

Breed Varchar (10

DOB DateTime

OwnerID Numeric (10 ) NOT NULL

Every column cannot be made NOT NULL ' as the attributes are absolutely required to be filled for storing information about the pets Only the primary key and the foreign key fields would be NOT NULL as they would always require a value

3 .9

ALTER TABLE PET ADD CONSTRAINT LINK_FK FOREIGN KEY (OwnerID ) REFERENCES PET_OWNER (OwnerID

3 .10

ALTER TABLE PET ADD CONSTRAINT LINK_FK_1 FOREIGN KEY (OwnerID REFERENCES PET_OWNER (OwnerID ) ON DELETE CASCADE

3 .11

CREATE TABLE PET_2

Name Varchar (20 ) Primary Key

Type Varchar (10

Breed Varchar (10

DOB DateTime

OwnerID Numeric (10 ) NOT NULL

3 .12

PET is a better design than PET_2 as names of PET_2 can be similar so it is usually not suggested to have Name ' as a primary key

3 .13

ALTER TABLE [dbo] .[PET] DROP CONSTRAINT [LINK_FK_1]

DROP TABLE PET_OWNER

3 .14

ALTER TABLE [dbo] .[PET] DROP CONSTRAINT...