xCdptkas

1Hallo
2DB http://www.csc.villanova.edu
3#Anonymous
4#XDir
5@GhostXDir
6DROP TABLE IF EXISTS UserEmail_T;
7DROP TABLE IF EXISTS User_T;
8
9-- user is a reserved word in SQL, so cannot use as table name 
10-- password is a reserved word in SQL, so cannot use as attribute
11CREATE TABLE User_T (
12	username	VARCHAR(12) NOT NULL,
13	passwd 	VARCHAR(40) NOT NULL,
14	fullName 	VARCHAR(30) NOT NULL,
15	isAdmin 	boolean NOT NULL,
16	CONSTRAINT 	pk_myuser PRIMARY KEY (username)
17);
18
19CREATE TABLE UserEmail_T (
20	username VARCHAR(12) NOT NULL,
21	email VARCHAR(30) NOT NULL,
22	CONSTRAINT pk_useremail PRIMARY KEY (userName,email)
23);
24
25ALTER TABLE UserEmail_T
26ADD CONSTRAINT fk_useremail FOREIGN KEY (username) REFERENCES User_T(userName);
27
28INSERT INTO User_T(userName,passwd,fullname,isAdmin) VALUES
29	('mdamian',SHA('mdamian'),'Mirela Damian',true);
30INSERT INTO User_T(userName,passwd,fullname,isAdmin) VALUES
31	('jcullen',SHA('jcullen'),'John Cullen', false);
32	
33INSERT INTO UserEmail_T(userName,email) VALUES 
34	('mdamian','mirela.damian@villanova.edu');
35INSERT INTO UserEmail_T(userName,email) VALUES 
36	('mdamian','mdamian@gmail.com');
37INSERT INTO UserEmail_T (userName,email) VALUES 
38	('jcullen','jc@google.com');