Question

Through the remaining assignments due in this course, you will be creating a simple database for tracking information about volunteers working and raising money for a community organization. This assignment requires that you create the initial table, called PERSON, to hold basic information about volunteers. You will be redefining the design and building the database in the upcoming unit assignments.

1.Use the mysqldump.exe command line tool to backup the data in your volunteer database.

• To access the mysqldump.exe tool, start the MySQL command line tool.
• Use the quit command to exit to the system command prompt.
• Run mysqldump.exe and direct the output of the backup to an appropriately named file.
• Create a new database and restore the database from the backup to the new database. Submit a screenshot to document the restoration of the data to a new database.

2. Write SQL CREATE TABLE statements and run them to create three new tables (ADDRESS, PHONE, AND EMAIL) to split out the contact information for your volunteers that is currently contained in the PERSON table.

• Each table should have a unique primary key and foreign key constraint reference to the PERSON table.

3.After creating the new tables (in Step 2), write and run SQL to insert the relevant data for the volunteers in the new tables.

4.Modify the PERSON table using the ALTER TABLE statement to remove the columns for the data moved to the new tables.

Answer 1

2.) CREATE TABLE phno(
firstname varchar[30],
phoneno int NOT NULL,
primary key (phoneno));
CREATE TABLE address (
apartmentno int,
line1 varchar[30],
line2 varchar[30],
landmark varchar[30],   
pin int,
foreign key (phoneno) references phno (phoneno) );
CREATE TABLE email (
fname varchar[30],
email varchar[40],
foreign key (phoneno) references phno (phoneno) );
3.) for table person
insert into person values('Rahul','sharma','Male',9139876587);
insert into person values ('James','charles','Male',8976057768);
insert into person values ('Surbhi','Jyoti','Female',7094351762);
OUTPUT:
Rahul|9139876587
James|8976057768
Surbhi|7094351762
for table address
insert into address values(20,'Shantivan Society','Lonavala','Railway Crossing',410401,8976057768);
insert into address value(42,'XYA housing society','Mumbai','near infinity mall',410099,9139876587);
insert into address values(72,'Bagh no 3','Lucknow','Hyundai showroom',226023,7094351762);
OUTPUT:
20|Shantivan Society|Lonavala|Railway Crossing|410401|8976057768
42|XYA housing society|Mumbai|near infinity mall|410099|9139876587
72|Bagh no 3|Lucknow|Hyundai showroom|226023|7094351762
for table email
insert into email values ('rahul','rahul123',9139876587);
insert into email values ('Surbhi','sjyoti45',7094351762);
insert into email values('james','jc5647',8976057768);
OUTPUT:
rahul|rahul123|9139876587
Surbh|sjyoti45|7094351762
james|jc5647|8976057768
4.) Alter table person
drop column col1,col2,.....;
(Please write the column names in the database to be deleted in place of col1,col2.... Since I don't know the column name in person table so I couldn't write them).