Translate each of the following E-R Diagrams into logical designs, following the

Question

Translate each of the following E-R Diagrams into logical designs, following the procedures listed in Slide Drck 15. You should create a separate database for each E-R diagram. Write all of the SQL necessary to create the database and the tables. Use the title of each problem as the database name. Also, write at .least 3 INSERT statements for EACH table, making up whatever data you want (as long as it is reasonable). Put ALL of your SQL code into one file and submit it on Moodle by 11.55 pm tonight for up to a point bonus. You con work with others on this, but you must submit YOUR OWN code.

Explanation / Answer

Problem 1: Movie Theatre

create database movie_theatre;

create table movie_theatre(name varchar2(20), street varchar2(20), city varchar2(20), state varchar2(20), zipcode number(10), tax_id varchar2(20), showtime

varchar2(10) references movie(showtime));

insert into movie_theatre values('PVR MULTIPLEX', 'STREET1', 'VISAKHAPATNAM','ANDHRA PRADESH', 530004, 1234,'11:00');
insert into movie_theatre values('VARUN INOX', 'STREET2', 'VISAKHAPATNAM','ANDHRA PRADESH', 530004, 5678,'11:00');
insert into movie_theatre values('PRASAD'S', 'STREET3', 'VISAKHAPATNAM','ANDHRA PRADESH', 530004, 6754,'11:00');

create table movie(title varchar2(20), year number(4), producer varchar2(20), id number(10), actors varchar2(50), showtime varchar2(10));

insert into movie values('GOD'S NOT DEAD 2', 2016, 'HAROLD CRONK' , 123, 'JESSIE METCALF','11:00');
insert into movie values('THE DARK HORSE', 2016, 'JAMES NAPIER' , 234, 'CLIFF CURTIS','15:00');
insert into movie values('MILES AHEAD', 2016, 'DON CHEADLE' , 567, 'DON CHEADLE','18:00');

Problem 2: Rock Band

create database rock_band;

create table band_members(firstname varchar2(20), lastname varchar2(20), nickname varchar2(20), ssn number(10), id number(10) references instrument(id));
insert into band_members values('xxx','xxx','xxx',100, 111);
insert into band_members values('yyy','yyy','yyy',200, 222);
insert into band_members values('zzz','zzz','zzz',300, 333);

create table instrument(brandname varchar2(20), type varchar2(20), id number(10);
insert into instrument values('aria', guitar, 111);
insert into instrument values('mapex', drum, 222);
insert into instrument values('aihara', flute, 333);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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