1. Write the CREATE table statements needed to implement the folowing designin t

Question

1. Write the CREATE table statements needed to implement the folowing designin the EX shema:

Table name: Members                                            

Primary Key: member_id

Attributes: first_name, last_name, address, city, state, phone.

Table name: Members_Groups

Attributes: member_id, group_id

Table name: Groups

PK: group_id

Attributes: group_name

**These tables provide for members of an association, and each member can be registered in one or more groups within the association.  There should be one row for each member in the members table and one row for each group in the Groups table.  The member ID and group ID colums are the primary keys for Members and Group tables.  And the Members_Group table relates each member to one or more groups.

When you create the tables, be sure to include the key constraints.  Also, include any null or default constraints that you think are neccessary.

2.  Write INSERT statements that add two rows to the Members table for member IDs 1 and 2, two rows to the Groups table for Group IDs 1 and 2, and three rows to the Group_Membership table: one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2.  Then, write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name.

3. Create sequences that can be used to number the member ID and group ID values starting with 3.

4. Write an INSERT statement that adds another row to the Groups table.  This statement should use the NEXTVAL pseudo colum to get the value for the next group ID from the sequence that you created in excercise 3.  Then, write a SELECT statement that gets all of the data for all the rows in the Groups table to make sure your sequence worked correctly.

5. Write an ALTER table statement that adds two new colums to the Members table: one colum for annual dues that provides for three digits to the left of the decimal point and two to the right; and one colum for the payment date.  The annual dues colum should have default of 52.50.

6.  Write an ALTER table statement that modifies the Groups table so the group name in each row has to be unique.  Then, re-run the INSERT statement that you used in excercise 4 to make sure it works.

Explanation / Answer

1. Write the CREATE table statements needed to implement the folowing designin the EX shema:

Table name: Members

Primary Key: member_id

Attributes: first_name, last_name, address, city, state, phone.

create table Members(member_id number primary key,first_name varchar2(20), last_name varchar2(20), address varchar2(50), city varchar2(20), state varchar2(20), phone varchar2(20));

Table name: Groups

PK: group_id

Attributes: group_name

create table Groups(group_id number primary key, group_name varchar2(20));

Table name: Members_Groups

Attributes: member_id, group_id

create table Members_Groups(member_id number FOREIGN KEY REFERENCES Members(member_id), group_id number FOREIGN KEY REFERENCES Groups(group_id))

2. Write INSERT statements that add two rows to the Members table for member IDs 1 and 2,

two rows to the Groups table for Group IDs 1 and 2,

and three rows to the Group_Membership table:

one row for member 1 and group 2; one for member 2 and group 1; and one for member 2 and group 2.

Then, write a SELECT statement that joins the three tables and retrieves the group name, member last name, and member first name.

insert into Members values(1,'A','B','ABCD','CDEF','EFGH','123-456-7890');

insert into Members values(2,'Y','Z','MNCD','XYEF','XYGH','123-456-7999');

insert into Groups values(1,'ABCD');

insert into Groups values(2,'MNCD');

insert into Members_Groups values(1,2);

insert into Members_Groups values(2,1);

insert into Members_Groups values(2,2);

select group_name,last_name,first_name

from Members, Groups,Members_Groups

where Members.member_id = Members_Groups.member_id

and Groups.group_id = Members_Groups.group_id;

3. Create sequences that can be used to number the member ID and group ID values starting with 3.

CREATE SEQUENCE member_id_seq

START WITH 3

INCREMENT BY 1

NOCACHE

NOCYCLE;

CREATE SEQUENCE group_id_seq

START WITH 3

INCREMENT BY 1

NOCACHE

NOCYCLE;

4. Write an INSERT statement that adds another row to the Groups table.

This statement should use the NEXTVAL pseudo colum to get the value for the next group ID from the sequence that you created in excercise 3.

Then, write a SELECT statement that gets all of the data for all the rows in the Groups table to make sure your sequence worked correctly.

INSERT INTO Groups

(group_id, group_name)

VALUES

(group_id_seq.nextval, 'MNOP');

select * from Groups order by group_id asc;

5. Write an ALTER table statement that adds two new colums to the Members table:

one colum for annual dues that provides for three digits to the left of the decimal point and two to the right;

and one colum for the payment date. The annual dues colum should have default of 52.50.

alter table Members add annual_dues number(5,2) default 52.50;

alter table Members add payment_date date;

6. Write an ALTER table statement that modifies the Groups table so the group name in each row has to be unique.

Then, re-run the INSERT statement that you used in excercise 4 to make sure it works. Computer Science.

alter table Groups modify group_name varchar2(20) NOT NULL UNIQUE;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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