CSIS 316
HOMEWORK: MYSQL, DATABASE DESIGN, CREATION ASSIGNMENT
TIFFANY HODGE
OVERVIEW
Please answer the following 11 questions in valid, syntactically correct PHP or MySQL code
snippets as required. Put your answers below each question in this file using text only (i.e., do
NOT run your code in PHP or MySQL and do NOT provide screenshots). Highlight your inputs
in yellow where _____ is indicated. Otherwise, highlight the entire answer in yellow. Treat each
question separately. Only include the code necessary to answer the question asked. If an essay is
required, then please use full sentences and provide references where asked in proper APA
format. There are 11 questions total. Each question is worth varying points as shown below.
INSTRUCTIONS
1. (3 points) What are all of the rules for primary keys in a table. Provide an example that is
different from one in the book.
1. A primary key must be0unique. So, the primary key column does not contain duplicate
values.
2. Every0relational table0has one and0only one primary key.
3. A primary key must be0NOT NULL. So, the primary key column does not accept null
values.
4. The primary key is0irreducible. There must not be a subset of the primary key that can
uniquely identify the attributes of a table.
5. A primary key can become a0foreign key0(a key that refers to the PRIMARY KEY of
another table).
6. The length of a primary key cannot exceed09000bytes in size.
Example: Account Number: In an account table, the primary key could be the unique
account number assigned to each account.
2. (3 points) What is the main purpose of a foreign key in a table? What are all of the rules of a
foreign key? Provide an example of one that is different from the book.
Foreign keys are a column or combination of columns that is used0to establish and enforce a link
between the data in two tables to control the data that can be stored in the foreign key table.
CREATE0TABLE0Orders (
OrderID int0NOT0NULL,
OrderNumber int0NOT0NULL,
PersonID int,
0PRIMARY0KEY0(OrderID),
0CONSTRAINT0FK_PersonOrder0FOREIGN0KEY0(PersonID)
0REFERENCES0Persons(PersonID)
);
Page 1 of 5
CSIS 316
3. (5 points) Describe the three (3) relationship types in a relational database system and
provide a specific example of each that is different from the book. Discuss the tables and
their attributes and their relationships.
The three types of relationships in a database are one-to-one, one-to-many, and many-to-
many. One-to-one relationships occur when each row in a table1 has only one related row in
table2. Example: CREATE TABLE Employee (
ID int PRIMARY KEY,
Name VARCHAR(50)
);
CREATE TABLE Salary (
EmployeeID int UNIQUE NOT NULL,
SalaryAmount int
);
ALTER TABLE Salary
ADD CONSTRAINT FK_Salary_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee (ID);
One-to-many relationships when one record in table 1 is related to one or more records in
table 2. However, one record in table 2 cannot be related to more than one record in table 1.
Example: CREATE TABLE dbo.city (
city_id int IDENTITY,
city varchar(50) NOT NULL,
country_id int NOT NULL,
CONSTRAINT PK_city PRIMARY KEY CLUSTERED (city_id)
)
ON [PRIMARY]
GO
CREATE TABLE dbo.country (
country_id int IDENTITY,
country varchar(50) NOT NULL,
CONSTRAINT PK_country PRIMARY KEY CLUSTERED (country_id)
)
ON [PRIMARY]
GO
Many-to-many relationships occurs when multiple records in one table are related to
multiple records in another table.0Example:
CREATE TABLE films (
film_id INT PRIMARY KEY
,title VARCHAR(50)
,director VARCHAR(50)
,year_released DATETIME
);
CREATE TABLE category (
category_id INT PRIMARY KEY
,name VARCHAR(50)
);
Page 2 of 5
CSIS 316
CREATE TABLE film_category (
film_id INT
,category_id INT
,CONSTRAINT film_cat_pk PRIMARY KEY (film_id, category_id)
,CONSTRAINT FK_film
FOREIGN KEY (film_id) REFERENCES films (film_id)
,CONSTRAINT FK_category
FOREIGN KEY (category_id) REFERENCES category (category_id)
); (Devart, n.d.)
4. (5 points) Why aren’t many-to-many relationships implemented in a relational database
management system? Do some research and explain using an example with tables and
attributes and provide at least one external reference in proper APA format.
Many-to-many relationships aren't directly implemented in relational database management
systems (RDBMS) because they violate the principles of atomicity and require a different
structural approach to maintain data integrity. In relational databases, tables are designed to
maintain atomicity, meaning each entry (row) should represent a single, unique entity. Many-to-
many relationships inherently involve multiple instances of one entity relating to multiple
instances of another entity, which cannot be represented directly in a relational table structure
without introducing redundancy or ambiguity.
Consider a scenario where we have two entities: Students and Courses. A student can enroll in
multiple courses, and a course can have multiple students enrolled. This scenario represents a
many-to-many relationship.
Tables:
Students
oStudentID (Primary Key)
oStudentName
Courses
oCourseID (Primary Key)
oCourseName
To resolve a many-to-many relationship in a relational database, an intermediary table (often
referred to as a junction table or associative entity) is used:
Student_Course
oStudentID (Foreign Key referencing Students table)
Page 3 of 5
CSIS 316
oCourseID (Foreign Key referencing Courses table)
Each record in the Student_Course table represents a specific enrollment of a student in a
course. This structure allows each student to be associated with multiple courses and each course
to be associated with multiple students, effectively resolving the many-to-many relationship into
two one-to-many relationships
Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Addison-Wesley.
5. (2 points) Create a database named mystore on the local MySQL server.
mysql -u your_username -p
CREATE DATABASE mystore;
SHOW DATABASES;
EXIT;
6. (2 points) Remove the UPDATE privilege on the table toys from the MySQL user mila.
1.Log in to MySQL with the mysql command using a user account that has administrative
privileges (GRANT privilege).
mysql -u your_admin_username -p
2.Revoke Update Privilege:
REVOKE UPDATE ON database_name.toys FROM 'mila'@'localhost';
3.Verify Revocation:
SELECT * FROM mysql.tables_priv WHERE User = 'mila' AND Table_name = 'toys' AND
Privilege_type = 'UPDATE';
4.Exit MySQL:
EXIT;
7. (2 points) Use SQL code to insert the values NULL, P001, and $6.99 into the PARTS table.
Insert the values NULL, P001, and $6.99 into the PARTS table
INSERT INTO PARTS (column1, column2, column3)
VALUES (NULL, 'P001', '$6.99');
8. (2 points) Use the mysql command to log into the server08 MySQL host with the username
tom, password P@44w0rd! and run the script insert.sql. Use only one command.
mysql -h server08 -u tom -pP@44w0rd! -e "source /path/to/insert.sql"
9. (2 points) Use SQL code to select the name, address, city, and state columns from the Client
table.
SELECT name, address, city, state
FROM Client;
10. (2 points) Use SQL code to select all attributes from the OrderDetails table where the
OrderID is greater than 100.
SELECT *
FROM OrderDetails
WHERE OrderID > 100;
Page 4 of 5
CSIS 316
11. (2 points) Use SQL code to select the ClientName from the Client table where the ClientID is
20 or 30.
SELECT ClientName
FROM Client
WHERE ClientID IN (20, 30);
References:
Devart. (n.d.). Types of relationships in SQL Server database. Devart. Retrieved July 9, 2024, from
https://blog.devart.com/types-of-relationships-in-sql-server-database.html
Page 5 of 5
Powered by TCPDF (www.tcpdf.org)
