The Public Safety office at CSUSM maintains a list of parking tickets issued to

Question

The Public Safety office at CSUSM maintains a list of parking tickets issued to vehicles parked illegally on the campus. The Table above shows a portion of this list for the fall semester. (Attribute names are abbreviated to conserve space.)

a. Based on the following Entity-Relationship (E-R) diagram, please create the appropriate cardinality notations

b. Create a database with multiple tables in your assigned database environment with records shown above in the parking ticket table. Indicate the primary keys and foreign keys in each table if possible.

Parking Ticket Table St ID N Date Code Ticket BRY 123 15634 16017 TRE 141 14987 16293 17892 St Lic Fine $25 $15 $100 L Name F Name Phone No Lic No 38249 10 17/12 11/13/12 10/05 12 11/18/12 12/13/12 Brown Thomas 111-7804FL 82453 Green Sally 391-1689 AL $25

Explanation / Answer

Answer)

Table is to be created based on the above schema diagram given and data inserted as the above sample shown.

Table Creation Scripts:

CREATE TABLE TICKET_CODE(Code int PRIMARY KEY, Fine varchar);

CREATE TABLE STUDENT(

STID int PRIMARY KEY,

LName varchar,

FName varchar,

PhoneNo varchar,  

LicNo varchar,

STLic varchar

);

CREATE TABLE

TICKET(

Ticket_no int PRIMARY KEY,

Date varchar,

Code int,

STID int,

FOREIGN KEY (Code) REFERENCES TICKET_CODE(Code),

FOREIGN KEY (STID) REFERENCES TICKET_CODE(STID)

);

Data insertion Script:

INSERT INTO TICKET_CODE VALUES (2,"$25");

INSERT INTO TICKET_CODE VALUES (1,"$15");

INSERT INTO TICKET_CODE VALUES (3,"$100");

INSERT INTO TICKET values("15634","11/13/12",2,38249);

INSERT INTO TICKET values("16017","10/17/12",1,38249);

INSERT INTO TICKET values("14987","10/05/12",3,82453);

INSERT INTO TICKET values("16293","11/18/12",1,82453);

INSERT INTO TICKET values("17892","12/13/12",2,82453);

INSERT INTO STUDENT values(38249,"Brown","Thomas","111-7804","FL","BRY 123");

INSERT INTO STUDENT values(82453,"Green","Sully","391-1689","AL","TRE 141");

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.