connect 'jdbc:derby://localhost:1527/studentsDB;create=true;user=manager;password=managerPWD;';

-- by Dr. Alvaro Monge
-- This SQL DDL script creates a database that stores students, their major(s), and computing account
-- It is a possible solution to part of Assignment #4 from Fall-2006.

DROP TABLE majorsChosen;
DROP TABLE accounts;
DROP TABLE students;
DROP TABLE majors;

CREATE TABLE majors
(
	mCode			CHAR(5) NOT NULL,
	mName			VARCHAR(50) NOT NULL,
	mUnitsRequired	INTEGER NOT NULL,
	mDescription	VARCHAR(100) NOT NULL,
	mYearCreated	INTEGER,
	CONSTRAINT majors_PK PRIMARY KEY(mCode),
	CONSTRAINT majors_unique_names UNIQUE(mName),
	CONSTRAINT majors_unique_description UNIQUE(mDescription)
   
);   


CREATE TABLE students
(
	SID				CHAR(10) NOT NULL,
	sFirstName		VARCHAR(30) NOT NULL,
	sLastName		VARCHAR(30) NOT NULL,
	sPhoneNumber	CHAR(12) NOT NULL,     -- that's:  123-456-7890
	sStreetAddress	VARCHAR(50) NOT NULL,
	sCity			VARCHAR(30) NOT NULL,
	sState			CHAR(2) NOT NULL,
	sZipCode		CHAR(10) NOT NULL,     -- that's a fixed size string like: 90840-8302
	CONSTRAINT students_PK PRIMARY KEY(SID),
	CONSTRAINT students_unique_person1 UNIQUE(sFirstName, sLastName, sStreetAddress, sCity, sState, sZipCode),
	CONSTRAINT students_unique_person2 UNIQUE(sFirstName, sLastName, sPhoneNumber)
);

CREATE TABLE accounts
(
	aHandle			CHAR(10) NOT NULL,
	aPassword		VARCHAR(15) NOT NULL,   -- will be encrypted later...  for now, assume application encrypts it
	aDiskQuota		INTEGER NOT NULL,       -- given in Megabytes
	aOwnerID		CHAR(10) NOT NULL,
	CONSTRAINT accounts_PK PRIMARY KEY(aHandle),
	CONSTRAINT accounts_one_account_per_ID UNIQUE(aOwnerID),
	CONSTRAINT accounts_students_FK FOREIGN KEY(aOwnerID) REFERENCES students(SID)
);

CREATE TABLE majorsChosen
(
	majorCode		CHAR(5) NOT NULL,
	studentID		CHAR(10) NOT NULL,
	CONSTRAINT majorsChosen_PK PRIMARY KEY(majorCode,studentID),
	CONSTRAINT majorsChosen_majors_FK FOREIGN KEY(majorCode) REFERENCES majors(mCode),
	CONSTRAINT majorsChosen_students_FK FOREIGN KEY(studentID) REFERENCES students(SID)
);


INSERT INTO students(SID,sLastName,sFirstName,sPhoneNumber,sStreetAddress,sCity,sState,sZipCode)
VALUES
    ('0000005629',  'Lane', 'Lois', '563-555-8212', '593 Kryptonite Blvd.', 'Metropolis', 'CA', '90844'),
    ('0000003762',  'Kent', 'Clark', '563-555-8212', '729 Steel St', 'Metropolis', 'CA', '90831'),
    ('0000001023',  'Parker', 'Peter', '563-555-8212', '762 Web Way', 'Huntington Beach', 'CA', '90700'),
    ('0000006720',  'Luthor', 'Lex', '563-555-8212', '9351 Swamp Alley', 'Huntington Beach', 'CA', '90715'),
    ('0000009123',  'Watson', 'Mary-Jane', '563-555-8212', '762 Web Way', 'Huntington Beach', 'CA', '90700'),
    ('0000001387',  'Osborn', 'Harry', '563-555-8212', '1 Main St', 'Huntington Beach', 'CA', '90701'),
    ('0000008623',  'Banner', 'Bruce', '563-555-8212', '1953 Cherry Ave', 'Long Beach', 'CA', '90843'),
    ('0000002385',  'Prince', 'Diana', '563-555-8212', '6153 Wonder Woman Blvd', 'Long Beach', 'NY', '11561'),
    ('0000006520',  'Wolverine', 'Logan', '563-555-8212', '7285 Mountain Loop', 'Pasadena', 'CA', '90023'),
    ('0000005638',  'Grey', 'Jeane', '563-555-8212', '832 Rose Blvd', 'Beverly Hills', 'CA', '90210'),
    ('0000007163',  'Storm', 'Ororo', '563-555-8212', '832 Rose Blvd', 'Beverly Hills', 'CA', '90210'),
    ('0000006329',  'Rogue', 'Marie', '563-555-8212', '7285 Mountain Loop', 'Pasadena', 'CA', '90023'),
    ('0000004629',  'Cyclops', 'Scott', '563-555-8212', '832 Rose Blvd', 'Beverly Hills', 'CA', '90210'),
    ('0000007263',  'Sabretooth', 'Victor', '563-555-8212', '1 Feline Dr', 'Big Bear', 'CA', '90445');

INSERT INTO majors(mCode,mName,mUnitsRequired,mDescription,mYearCreated)
VALUES
   ('CS', 'Computer Science', 126, 'The design and implementation of software systems. Theory and practice', 1980),
   ('CpE', 'Computer Engineering', 130, 'Design, testing, analysis of computer hardware. Theory and practice', 1977),
   ('PHYS', 'Physics', 130, 'Physics attempts to describe the natural world by the application of the scientific method', 1962),
   ('MUS', 'Music', 123, 'Performance-focused study of musics, its methods and history', 1950),
   ('MAE', 'Aerospace Engineering', 123, 'Applied math and physics to study of aerospace', 1950);


INSERT INTO majorsChosen(majorCode,studentID)
VALUES
   ('CS', '0000002385'),
   ('CS', '0000007163'),
   ('PHYS', '0000004629'),
   ('PHYS', '0000001023'),
   ('MAE', '0000001023'),
   ('CS', '0000001023'),
   ('MUS', '0000009123');


INSERT INTO accounts(aHandle, aPassword, aDiskQuota, aOwnerID)
VALUES
   ('banner', 'Htii2691', 100, '0000008623'),
   ('mjwatson', 'xiIe28', 200, '0000009123'),
   ('sabretooh', 'MC28a;k8', 100, '0000007263'),
   ('pparker', '89u21jd%', 200, '0000001023'),
   ('ostorm', 'Lme72;KJ', 500, '0000007163'),
   ('hosborn', '3Hme.9ip', 100, '0000001387'),
   ('llane', 'yeaMip89', 500, '0000005629');

disconnect;
exit;