Personal Playground Database System: Codio Platform

This article explains the tasks involved in creating a personal playground database system using the Codio platform. It covers inserting, updating, and deleting records in tables, creating new tables, and connecting them using foreign keys. It also includes SQL queries to retrieve specific data from the database.

Running head: PERSONAL PLAYGROUND DATABASE SYSTEM
Personal Playground Database System: Codio Platform
Name of the Student
Name of the University

1PERSONAL PLAYGROUND DATABASE SYSTEM
Table of Contents
Task 1: Insert Record to the Person Table.......................................................................................2
Task 2: Alter the Person Table........................................................................................................2
Task 3: Update Records in the Person Table...................................................................................3
Task 4: Delete Records from Person Table.....................................................................................3
Task 5: Alter the Contact List Table................................................................................................3
Task 6: Update Records in the Contact List Table..........................................................................4
Task 7: Update Records in the Contact List Table..........................................................................4
Task 8: Insert Records to Contact List Table..................................................................................5
Task 9: Create the Image Table.......................................................................................................6
Task 10: Create the Message-Image Intersection Table..................................................................6
Task 11: Insert Records to Image Table..........................................................................................7
Task 12: Insert Records to Message-Image Table...........................................................................8
Task 13: Find All of the Messages that Michael Phelps Sent.........................................................9
Task 14: Find the Number of Messages Sent for Every Person....................................................10
Task 15: Messages that Have At Least One Image Attached........................................................11
Bibliography:.................................................................................................................................12

2PERSONAL PLAYGROUND DATABASE SYSTEM
Task 1: Insert Record to the Person Table
insert into Person (first_name, last_name) Values ('Zayda', 'Bajana');
The MySQL allows the user to inert data into the table after creating the table. The syntax
for inserting a value in the table is insert into [Table Name] (column1, clumn2, …) Values
(value1, value2, …); The person_id is the primary key of the Person table. The person_id is set
as autoincrement. That is why the person_id value will not be inserted. The database will insert
the values automatically. As not all the values will be inserted using insert into query, it is
essential to specify the column name such as (first_name, last_name). Otherwise, (column1,
clumn2, …) syntax can be omitted.
Task 2: Alter the Person Table
alter table Person ADD address varchar(25) NOT NULL;
Altering a table is an essential part of database. Alter tables are used for various reasons
like adding column, modifying column and many more. The syntax for using alter table query is
alter table [Table Name] Add/Modify [Column Name] [Data Type]; Alter table is also used for
dropping a column with syntax ALTER TABLE [Table Name] DROP COLUMN [Column
Name]; In this case, the alter table query has been used for adding a new column, address with
varchar(25) datatype, to the Person table.

3PERSONAL PLAYGROUND DATABASE SYSTEM
Task 3: Update Records in the Person Table
Update Person Set address = '12/1 Main Street' Where first_name='Zayda' and
last_name='Bajana';
In the domain of MySQL, ‘update’ query is used mostly because of updating a value in
the column. The task is to update the newly added value for user with first name Zayada and last
name Bajana. The syntax for updating a value is Update [Table Name] Set [Column Name] =
[Value] Where [Condition]; In this case, the address value is updated to ‘12/1 Main Street’. The
query checks whether the row with with first name Zayada and last name Bajana exists or not.
Task 4: Delete Records from Person Table
Delete From Person Where first_name='Diana' and last_name='Taurasi';
The delete statement has bee used to delete the row with first name Diana and last name
Taurasi. The syntax is DELETE FROM [Table Name] WHERE [Condition]; The MySQL
database terminal first checks the table Person exists or not and then checks whether the
condition is valid or not. If the condition is valid, the database terminal deletes the row. The
database also checks the referential integrity. If the deletion of a row affects the child column
then, based on delete option, the child value is set.
Task 5: Alter the Contact List Table
alter table contact_list ADD favorite varchar(10) NULL;

4PERSONAL PLAYGROUND DATABASE SYSTEM
Alter table is also used for dropping a column with syntax ALTER TABLE [Table Name]
DROP COLUMN [Column Name]; Altering a table is an essential part of database. Alter tables
are used for various reasons like adding column, modifying column and many more. The syntax
for using alter table query is alter table [Table Name] Add/Modify [Column Name] [Data Type];
In this case, the alter table query has been used for adding a new column, favorite with
varchar(10) datatype, to the Contact_List table.
Task 6: Update Records in the Contact List Table
update contact_list Set favorite = 'y' Where contact_id = 1;
This update statement has used to set the favorite as y for the contact lists which has 1 as
contact id. The contact id 1 refers to Michael Philips. In the domain of MySQL, ‘update’ query is
used mostly because of updating a value in the column. The syntax for updating a value is
Update [Table Name] Set [Column Name] = [Value] Where [Condition]; The update command
affects only a single row means that only a single row has contact id as 1.
Task 7: Update Records in the Contact List Table
update contact_list Set favorite = 'n' Where contact_id != 1;

5PERSONAL PLAYGROUND DATABASE SYSTEM
In this update statement, the favorite is set as n for the rows which does not have 1 as
contact_id. From the outcome, 1 row affected, it can be stated that only one entry was there
which did not have contact_id as 1. The ‘!=’ operator is used as the negative of ‘=’. This operator
has been used for checking whether the value of contact_id is other than 1 or not. The set
operator is used for updating the value of favorite as ‘n’.
Task 8: Insert Records to Contact List Table
insert into contact_list (person_id, contact_id, favorite) Values (4, 6, 'n');
insert into contact_list (person_id, contact_id, favorite) Values (5, 1, 'y');
insert into contact_list (person_id, contact_id, favorite) Values (6, 1, 'y');
The insert statement has been used for inserting three new entries in the contact_list table.
The contact_list table’s contact_id is an auto increment column. As before, the columns have
been mentioned. The values will be inserted for these columns only. The contact_id will get
automatic integer values. If the contact_id is 1 then favorite will be ‘y’, otherwise it will be ‘n’.

6PERSONAL PLAYGROUND DATABASE SYSTEM
Task 9: Create the Image Table
create table image (
image_id int(8) UNSIGNED AUTO_INCREMENT Primary Key,
image_name varchar(50) NOT NULL,
image_location varchar(250) NOT NULL
);
The create table statement has been used for creating an image table. The image table has
three columns named image_id, image_name and image_location. The image_id is the primary
key of the table. The ‘int(8) UNSIGNED AUTO_INCREMENT’ statement states that image_id
will be an integer value with only positive value. The image location can be lengthy that is why
it has varchar(250) data type. That means, location can store up to 250 characters.
Task 10: Create the Message-Image Intersection Table
create table message_image (
message_id int(8) UNSIGNED,
image_id int(8) UNSIGNED,
PRIMARY KEY (message_id, image_id),

7PERSONAL PLAYGROUND DATABASE SYSTEM
FOREIGN KEY (message_id) REFERENCES message(message_id) ON DELETE
CASCADE,
FOREIGN KEY (image_id) REFERENCES image(image_id) ON DELETE CASCADE
);
The message_image table has been used for connecting the image and message tables. It
can be considered as a bridge table in this database. There is no other function for this table. The
image_id and message_id are the foreign keys of image and message table primary keys
respectively. In this table creation statement, the primary is composite one. That is why the
syntax Primary Key(column 1, column 2, …) has been used.
Task 11: Insert Records to Image Table
insert into image (image_name, image_location) Values ('Lily Flower',
'/upload/image/flower');
insert into image (image_name, image_location) Values ('London Bridge',
'/upload/image/bridge');
insert into image (image_name, image_location) Values ('Bob Marley',
'/upload/image/singer_bob_marley');

8PERSONAL PLAYGROUND DATABASE SYSTEM
insert into image (image_name, image_location) Values ('Ha Ha',
'/upload/image/reat_ha_ha');
insert into image (image_name, image_location) Values ('Sad', '/upload/image/reat_sad');
insert into image (image_name, image_location) Values ('Ifel Tower',
'/upload/image/ifel_tower');
If a table does not have any primary key, then it is of no use in the real world. That is
why the newly created image table must be filled with data. The insert into queries have been
used for inserting five records into the table. Same as other tables, the image_id has been left
empty as it will be recorded by the DBMS automatically.
Task 12: Insert Records to Message-Image Table
insert into message_image (message_id, image_id) Values (1, 1);
insert into message_image (message_id, image_id) Values (7, 3);
insert into message_image (message_id, image_id) Values (6, 5);
insert into message_image (message_id, image_id) Values (4, 6);

9PERSONAL PLAYGROUND DATABASE SYSTEM
insert into message_image (message_id, image_id) Values (5, 2);
The message_image table must have values so that the image and message tables can be
connected. It is very important to understand the concept of referential integrity. If the values of
image_id and message_id are not present in the parent table then the insert statement will not
work.
Task 13: Find All of the Messages that Michael Phelps Sent
SELECT
CONCAT(T1.first_name, ' ', T1.last_name) As Sender,
CONCAT(T2.first_name, ' ', T2.last_name) As Reciever,
m.message_id,
m.message,
m.send_datetime
FROM message as m

10PERSONAL PLAYGROUND DATABASE SYSTEM
Inner Join Person as T1 on m.sender_id = T1.person_id
Inner Join Person as T2 on m.receiver_id = T2.person_id
WHERE sender_id =1;
This query has a unique characteristic. The select query will show the values of sender_id
and reciver_id as different values. The T1 and T2 has been used to specify the inner join table.
Based on the joining condition, the sender and receiver id is identified.
Task 14: Find the Number of Messages Sent for Every Person
Select Count(m.message_id) AS 'Count of messages', P.person_id AS 'Person ID',
P.first_name AS 'First Name', P.last_name AS 'Last Name' From message m inner join Person P
on m.sender_id = P.person_id Group By P.person_id;

11PERSONAL PLAYGROUND DATABASE SYSTEM
The count function has been used for counting the number of messages sent by a user.
Along with the count of message, the person_id, first and last name of person has been shown.
Task 15: Messages that Have At Least One Image Attached
Select m.message_id, m.message, m.send_datetime, i.image_name, i.image_location
From message m inner join message_image mi on m.message_id = mi.message_id inner join
image i on mi.image_id = i.image_id;
The inner join is used for selecting the values that are present in both the tables.

12PERSONAL PLAYGROUND DATABASE SYSTEM
Bibliography:
Achpal, A., Kumar, V. B., & Mahesh, K. (2016). Modeling Ontology Semantic Constraints in
Relational Database Management System. In Proceedings of the International
MultiConference of Engineers and Computer Scientists (Vol. 1).
Veikkolainen, T., Pesonen, L. J., & Evans, D. A. (2014). PALEOMAGIA: A PHP/MYSQL
database of the Precambrian paleomagnetic data. Studia Geophysica et Geodaetica, 58(3),
425-441.