PERSON: Iname, fname, rname, idnum, private, linkblue lname, fname, rname are variable length strings idnum is type INT and is a key attribute private is BOOLEAN linkblue is string of 8 characters ADDR: idnum, adtype, streetaddr, city, state, country, zip idnum is a foreign key to PERSON.idnum adtype is a string in the set {"bill", "permanent", "local"} streetaddr is a variable length string that includes #, street, and maybe apt # state is a 2 letter char denoting the state country is a 2 letter char denoting country zip is string no bigger than 10 characters. ADVISOR: student, advisor student is a foreign key to PERSON.idnum advisor is a foreign key to PERSON.idnum DIGITAL: smtype, idnum, smaddr smtype is a string in the set {"email", "facebook", "twitter"} idnum is a foreign key to PERSON.idnum smaddr is a variable length string There is a primary key on the pair (smtype, idnum) CREATE TABLE PERSON(idnum INT NOT NULL PRIMARY KEY, Iname VARCHAR (40), fname VARCHAR(40), rname VARCHAR (40), private BOOLEAN, linkblue CHAR (8)); CREATE TABLE ADDR(idnum INT, adtype SET('bill', 'permanent', 'local'), streetaddr VARCHAR (50), city VARCHAR (50), state CHAR(2), zip VARCHAR(10), country CHAR(2), PRIMARY KEY(idnum, adtype), FOREIGN KEY(idnum) REFERENCES PERSON(idnum)); CREATE TABLE ADVISOR(student INT, advisor INT, PRIMARY KEY (student, advisor), FOREIGN KEY(student) REFERENCES PERSON(idnum), FOREIGN KEY(advisor) REFERENCES PERSON (idnum)); 4. CREATE TABLE DIGITAL(smtype SET('email', 'facebook', 'twitter'), idnum INT, smaddr VARCHAR(50), PRIMARY KEY(smtype, idnum), FOREIGN KEY(idnum) REFERENCES PERSON (idnum)); 5. INSERT INTO PERSON values(1, "Briggs, Logan, Robert, 98120534"); INSERT INTO PERSON values(2, "Salmon, James, Tyler, 97039814"); 6. INSERT INTO ADDR values(1, bill, 100 S Limestone, San Jose", "Ca, 90748, US"); INSERT INTO ADDR values(1, "local, "310 Main Street", "Birmingham, "Al, "41043", "US"); 7. INSERT INTO ADVISOR values(1, 11); INSERT INTO ADVISOR values(2, 10); 8. INSERT INTO DIGITAL values("email", 1, Logan Briggs1@gmail.com); INSERT INTO DIGITAL values(twitter, 1, LB_53); INSERT INTO DIGITAL values("email", 2, JamesSalmon@yahoo.com); INSERT INTO DIGITAL values("twitter, 2, SwmngSalmon); 9. ALTER TABLE ADVISOR ADD COLUMN (sdate DATE, edate DATE); #10 What is the value for the pre-existing tuple's sdate and edate entry after you execute the DDL statement from #9? 11-15: 10 pts each These queries should work for ANY data that might be present - not just the data you inserted above. [ADD: I updated this on Monday Feb17 to reflect "people" vs. "student".] ----------------------------- - - - - - - #11 Write the DML statement to query the database for all people with email addresses. Output their first and last name and their email address - only those 3 pieces of information. #12 Write the DML statement to query the database for all people with no billing address
