mirror of
https://github.com/rachelramin/Lifeline-Text-Program.git
synced 2026-11-05 17:02:35 -05:00
195 lines
7.2 KiB
Transact-SQL
Vendored
195 lines
7.2 KiB
Transact-SQL
Vendored
--Create and drop tables for login, user info and contact list.
|
||
|
||
|
||
--Drop statements with If-Exits to check and not drop if the table is not in the server.
|
||
--The drop statements are ordered so no drop constraint statments are needed
|
||
|
||
IF EXISTS (SELECT 1 FROM sysobjects
|
||
WHERE id=object_id('Contact_List') AND type='U')
|
||
DROP TABLE Contact_List
|
||
GO
|
||
|
||
IF EXISTS (SELECT 1 FROM sysobjects
|
||
WHERE id=object_id('Contact_Info') AND type='U')
|
||
DROP TABLE Contact_Info
|
||
GO
|
||
|
||
IF EXISTS (SELECT 1 FROM sysobjects
|
||
WHERE id=object_id('User_Info') AND type='U')
|
||
DROP TABLE User_Info
|
||
GO
|
||
|
||
IF EXISTS (SELECT 1 FROM sysobjects
|
||
WHERE id=object_id('User_Login') AND type='U')
|
||
DROP TABLE User_Login
|
||
GO
|
||
|
||
|
||
|
||
--All 4 tables are created, along with the Primary key constraints for esch table.
|
||
|
||
CREATE TABLE User_Login
|
||
(
|
||
User_ID_Num uniqueidentifier not null,
|
||
User_Email varchar(60) not null,
|
||
User_Password varchar(30) not null,
|
||
|
||
Constraint pk_User_ID_Num PRIMARY KEY (User_ID_Num)
|
||
)
|
||
GO
|
||
|
||
CREATE TABLE User_Info
|
||
(
|
||
User_ID_Num uniqueidentifier not null,
|
||
User_First_Name varchar(30) not null,
|
||
User_Last_Name varchar(30) not null,
|
||
User_Phone_Num varchar(10) not null,
|
||
|
||
--Constraint pk_User_ID_Num PRIMARY KEY (User_ID_Num)
|
||
)
|
||
GO
|
||
|
||
CREATE TABLE Contact_List
|
||
(
|
||
User_ID_Num uniqueidentifier not null,
|
||
Contact_List_ID uniqueidentifier not null,
|
||
Contact_ID_1 uniqueidentifier not null,
|
||
Contact_ID_2 uniqueidentifier not null,
|
||
Contact_ID_3 uniqueidentifier not null,
|
||
Contact_ID_4 uniqueidentifier not null,
|
||
Contact_ID_5 uniqueidentifier not null,
|
||
|
||
Constraint pk_Contact_List_ID PRIMARY KEY (Contact_List_ID)
|
||
|
||
)
|
||
GO
|
||
|
||
CREATE TABLE Contact_Info
|
||
(
|
||
Contact_ID uniqueidentifier not null,
|
||
Contact_First_Name varchar(30) not null,
|
||
Coantact_Last_Name varchar(30) not null,
|
||
Contact_Email varchar(50) not null,
|
||
Contact_Phone_Num varchar(10) not null,
|
||
|
||
Constraint pk_Contact_ID PRIMARY KEY (Contact_ID)
|
||
|
||
)
|
||
GO
|
||
|
||
--Tables are altered with the creation of the foregin keys for all the tables.
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_Contact_ID_1
|
||
FOREIGN KEY (Contact_ID_1)
|
||
REFERENCES Contact_Info(Contact_ID)
|
||
go
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_Contact_ID_2
|
||
FOREIGN KEY (Contact_ID_2)
|
||
REFERENCES Contact_Info(Contact_ID)
|
||
go
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_Contact_ID_3
|
||
FOREIGN KEY (Contact_ID_3)
|
||
REFERENCES Contact_Info(Contact_ID)
|
||
go
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_Contact_ID_4
|
||
FOREIGN KEY (Contact_ID_4)
|
||
REFERENCES Contact_Info(Contact_ID)
|
||
go
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_Contact_ID_5
|
||
FOREIGN KEY (Contact_ID_5)
|
||
REFERENCES Contact_Info(Contact_ID)
|
||
go
|
||
|
||
ALTER TABLE Contact_List
|
||
ADD Constraint fk_User_ID_Num
|
||
FOREIGN KEY (User_ID_Num)
|
||
REFERENCES User_Login(User_ID_Num)
|
||
go
|
||
|
||
ALTER TABLE User_Info
|
||
ADD Constraint fk_User_ID_Num_2
|
||
FOREIGN KEY (User_ID_Num)
|
||
REFERENCES User_Login(User_ID_Num)
|
||
go
|
||
|
||
--Joins linking the tables and consrtints together are created.
|
||
|
||
--Join User_ID from User info, and Contact list ID form Contact List
|
||
SELECT User_Info.User_ID_Num, Contact_List.Contact_List_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_2 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5 INNER JOIN
|
||
User_Login ON Contact_List.User_ID_Num = User_Login.User_ID_Num INNER JOIN
|
||
User_Info ON User_Login.User_ID_Num = User_Info.User_ID_Num
|
||
|
||
Go
|
||
|
||
|
||
--Join Contact_ID_1 from list to contact ID form Contact Info
|
||
SELECT Contact_List.Contact_ID_1, Contact_Info.Contact_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_2 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5 INNER JOIN
|
||
User_Login ON Contact_List.User_ID_Num = User_Login.User_ID_Num INNER JOIN
|
||
User_Info ON User_Login.User_ID_Num = User_Info.User_ID_Num
|
||
GO
|
||
|
||
|
||
--Join Contact_ID_2 from list to contact ID form Contact Info
|
||
SELECT Contact_List.Contact_ID_2, Contact_Info.Contact_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_2 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5
|
||
GO
|
||
|
||
|
||
--Join Contact_ID_3 from list to contact ID form Contact Info
|
||
SELECT Contact_List.Contact_ID_3, Contact_Info.Contact_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_2
|
||
AND Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5
|
||
GO
|
||
|
||
|
||
--Join Contact_ID_4 from list to contact ID form Contact Info
|
||
SELECT Contact_List.Contact_ID_4, Contact_Info.Contact_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_2
|
||
AND Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5
|
||
GO
|
||
|
||
|
||
--Join Contact_ID_5 from list to contact ID form Contact Info
|
||
SELECT Contact_List.Contact_ID_5, Contact_Info.Contact_ID
|
||
FROM Contact_Info INNER JOIN
|
||
Contact_List ON Contact_Info.Contact_ID = Contact_List.Contact_ID_1 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_2
|
||
AND Contact_Info.Contact_ID = Contact_List.Contact_ID_3 AND
|
||
Contact_Info.Contact_ID = Contact_List.Contact_ID_4 AND Contact_Info.Contact_ID = Contact_List.Contact_ID_5
|
||
GO
|
||
|
||
--Join User_ID from User login and User_ID from User_Info
|
||
SELECT User_Login.User_ID_Num, User_Info.User_ID_Num AS Expr1
|
||
FROM User_Login INNER JOIN
|
||
User_Info ON User_Login.User_ID_Num = User_Info.User_ID_Num INNER JOIN
|
||
Contact_List ON User_Login.User_ID_Num = Contact_List.User_ID_Num INNER JOIN
|
||
Contact_Info ON Contact_List.Contact_ID_1 = Contact_Info.Contact_ID AND Contact_List.Contact_ID_2 = Contact_Info.Contact_ID
|
||
AND Contact_List.Contact_ID_3 = Contact_Info.Contact_ID AND
|
||
Contact_List.Contact_ID_4 = Contact_Info.Contact_ID AND Contact_List.Contact_ID_5 = Contact_Info.Contact_ID
|
||
Go
|
||
|
||
|