|
T. Andrew Yang
|
Last updated: 6/3/2024: first posted |
||||||
CSCI
4333 Design of Database Systems
NOTE: When preparing your answers, you are welcome to use
any resources, including the text books and the class notes. However, make
sure you properly cite the work of other researchers or professionals.
Visit https://sceweb.uhcl.edu/yang/citing.htm for more information about cited references.
Warning: Missing or improperly cited references in your answers
will result in poor scores.
Note about AI tools: You
may use AI tools such as ChatGPT as a starting point while putting together
your own answer; however, simply copying the answer produced by ChatGPT (or
any AI tool) is considered cheating.
Total: 100 points
1.
(10 pts)
Visit the class discussion group in the Blackboard. Post a message with your
full name as the subject line. In your post, briefly introduce yourself,
including your full name, your major, and one or two items that you most desire
to learn in this class. Throughout this class, you shall regularly participate
in the discussion group to find recent announcements, reminders, and
discussions.
2. Explain what each of
the following means in the relational model.
2.1.
(5 pts) relation
2.2.
(5 pts) attribute
2.3.
(5 pts) tuple
2.4.
(5 pts) value
2.5.
(5 pts) foreign key
2.6.
(5 pts) primary key
3. In this exercise, you will
demonstrate that you are able to connect to the dcm.uhcl.edu server and access
the toyu database.
3.1.
Check
out the instructions in Using MySQL
database to learn how to connect to dcm.uhcl.edu and access the toyu database.
Note: Your account id is your
last name followed by the first letter of your first name. For example, John
Doe’s account name is doej.
The password is Sce+your id + !! (e.g., Sce1234567!!, if the person’s id is
1234567.
3.2.
A
database called toyu
is already installed in your user account on dcm.uhcl.edu.
Note: The SQL statements used to
create the toyu database are in this file: createtoyu.sql.txt.
3.3.
(10 pts) List all the students majoring in
“CSCI”.
+--------+-------+-------+-------+-------+-----+---------+
| stuId | fname | lname | major | minor | ach | advisor |
+--------+-------+-------+-------+-------+-----+---------+
| 100000 | Tony | Hawk
| CSCI | CINF | 40
| 1011 |
| 100001 | Mary | Hawk
| CSCI | CINF | 35 | 1011 |
| 100002 | David | Hawk | CSCI
| ITEC | 66 |
1012 |
+--------+-------+-------+-------+-------+-----+---------+
3.4.
(10 pts) List the department name and
number of staff of all the departments in “CSE”.
+------------------------------+----------+
| deptName | numStaff
|
+------------------------------+----------+
| Computer Information Systems
| 5 |
| Computer Science | 12 |
| Information Technology |
4 |
| Mathematics | 7 |
+------------------------------+----------+
3.5.
(10 pts) List the departments that have
more than 10 staff members.
+----------+------------------+------------+----------+
| deptCode
| deptName
| schoolCode | numStaff
|
+----------+------------------+------------+----------+
| CSCI | Computer Science | CSE |
12 |
| ENGL | English | HSH |
12 |
+----------+------------------+------------+----------+
3.6.
(10 pts) List the students that have more
than 60 credits (ach).
+--------+-------+---------+-------+-------+-----+---------+
| stuId | fname | lname | major |
minor | ach | advisor |
+--------+-------+---------+-------+-------+-----+---------+
| 100002 | David | Hawk | CSCI
| ITEC | 66 |
1012 |
| 100004 | Larry | Johnson |
ITEC | NULL | 66
| 1017 |
| 100008 | Bill | Ching
| ARTS | NULL | 90 | NULL |
| 100009 | Linda | King | ARTS
| CSCI | 125 | 1018 |
+--------+-------+---------+-------+-------+-----+---------+
3.7.
(10 pts) List the students who have “Mary
Tran” as the advisor.
+--------+-------+-------+-------+-------+-----+---------+
| stuId | fname | lname | major | minor | ach | advisor |
+--------+-------+-------+-------+-------+-----+---------+
| 100002 | David | Hawk | CSCI
| ITEC | 66 |
1012 |
+--------+-------+-------+-------+-------+-----+---------+
3.8.
(10 pts) List the students who are not
assigned an advisor yet.
+--------+-----------+-------+-------+-------+-----+---------+
| stuId | fname | lname | major
| minor | ach | advisor |
+--------+-----------+-------+-------+-------+-----+---------+
| 100003 | Catherine | Lim | ITEC
| CINF | 20 |
NULL |
| 100007 | Ben |
Zico | NULL | NULL
| 16 | NULL |
| 100008 | Bill | Ching | ARTS | NULL
| 90 | NULL |
+--------+-----------+-------+-------+-------+-----+---------+
Go to
the Index
Total:
100 points
In this exercise, you will
demonstrate that you are able to install the toyu
database into your own account on dcm.uhcl.edu and experiment with various
operations.
1. Check out the instructions in Creating
your own toyu database to learn how to create
your own toyu database using a script.
Note: Your account id is your
last name followed by the first letter of your first name. For example, John
Doe’s account name is doej.
The password is Sce+your id + !! (e.g., Sce1234567!!, if the person’s id is
1234567.
2. Run each of the
following statements against the database that was created in the previous
step.
For each of the
statements, do the following:
(a) Attach a
screenshot showing the query and the result.
(b) Explain what the
SQL statement does. NOTE: Your explanation should be meaningful to people who
do not know SQL.
2.1.
(10 pts) select fname,lname from student where major='ITEC' and
credits>60;
+-------+---------+
| fname | lname |
+-------+---------+
| Larry
| Johnson |
+-------+---------+
2.2.
(10 pts) select fname, lname, classId
from student s, enroll e where s.stuId=e.stuId and grade='A';
+---------+---------+---------+
| fname | lname | classId |
+---------+---------+---------+
|
Tony | Hawk |
10000 |
|
Tony | Hawk |
10001 |
|
Larry | Johnson | 10003 |
|
Lillian | Johnson | 10005 |
+---------+---------+---------+
2.3.
(10 pts) select * from course
NATURAL JOIN class;
+----------+--------+--------+-------------------------------------+---------+---------+----------+------+-------+------+
| courseId | rubric | number | title | credits | classId | semester | year | facId
| room |
+----------+--------+--------+-------------------------------------+---------+---------+----------+------+-------+------+
| 2000 | CSCI | 3333
| Data Structures
| 3 | 10000 | Fall | 2019 |
1011 | D241 |
| 2001 | CSCI | 4333
| Design of Database Systems
| 3 | 10001 | Fall | 2019 |
1011 | D242 |
| 2002 | CSCI | 5333
| DBMS
| 3 | 10002 | Fall | 2019 |
1012 | D136 |
| 2020 | CINF | 3321
| Introduction to Information Systems | 3 |
10003 | Fall | 2019 | 1014 | D241 |
| 2021 | CINF | 4320
| Web Application Development
| 3 | 10004 | Fall | 2019 |
1014 | D241 |
| 2040 | ITEC | 3335
| Database Development
| 3 | 10005 | Fall | 2019 |
1015 | D237 |
| 2041 | ITEC | 3312
| Introduction to Scripting
| 3 | 10006 | Fall | 2019 |
1019 | D217 |
| 2060 | ENGL | 1410
| English I |
4 | 10007 | Fall | 2019 |
1020 | B101 |
| 2080 | ARTS | 3311
| Hindu Arts
| 3 | 10008 | Fall | 2019 |
1018 | D241 |
| 2000 | CSCI | 3333
| Data Structures
| 3 |
11000 | Spring | 2020 | 1011 | D241 |
| 2001 | CSCI | 4333
| Design of Database Systems
| 3 | 11001 | Spring | 2020 |
1012 | D242 |
| 2002 | CSCI | 5333
| DBMS
| 3 | 11002 | Spring | 2020 |
1013 | D136 |
| 2020 | CINF | 3321
| Introduction to Information Systems | 3 |
11003 | Spring | 2020 | 1016 | D217 |
| 2061 | ENGL | 1311
| English II
| 3 | 11004 | Spring | 2020 |
1018 | B101 |
+----------+--------+--------+-------------------------------------+---------+---------+----------+------+-------+------+
2.4.
(10 pts) select year, count(*)
from course NATURAL JOIN class group by year;
+------+----------+
| year |
count(*) |
+------+----------+
| 2019
| 9 |
| 2020
| 5 |
+------+----------+
2.5.
(10 pts) select facid, year, count(*) from course NATURAL JOIN class group
by facid, year having count(*) > 1;
+-------+------+----------+
| facid | year | count(*) |
+-------+------+----------+
| 1011 | 2019 | 2 |
| 1014 | 2019 | 2 |
+-------+------+----------+
3. Given each of the
following tasks, come up with the SQL statement, run the statement, and attach
a screenshot of the query and the returned result.
3.1.
(10 pts) Revise the query in
2.1 above by printing not only the student’s name but also the student’s
advisor’s name and department.
+-------+---------+---------+-------+----------+
| fname | lname | fname | lname | deptcode |
+-------+---------+---------+-------+----------+
| Larry
| Johnson | Deborah | Gump | ITEC |
+-------+---------+---------+-------+----------+
3.2.
(10 pts) Revise the statement
in step 2.2 above such that not only the student’s name and classId,
but also the rubric, number, and title of the class are printed in the result.
+---------+---------+---------+--------+--------+-------------------------------------+
| fname | lname | classId | rubric | number | title |
+---------+---------+---------+--------+--------+-------------------------------------+
|
Tony | Hawk |
10000 | CSCI | 3333 | Data Structures |
|
Tony | Hawk |
10001 | CSCI | 4333 | Design of Database Systems |
| Larry | Johnson |
10003 | CINF | 3321 | Introduction to Information Systems |
|
Lillian | Johnson | 10005 | ITEC | 3335
| Database Development
|
+---------+---------+---------+--------+--------+-------------------------------------+
3.3.
(10 pts) Revise the statement
in 2.3 above such that the query returns the room, the year, and the number of
classes held in that room during that year.
+------+------+----------+
| room
| year | count(*) |
+------+------+----------+
| D241
| 2019 | 4 |
| D242
| 2019 | 1 |
| D136
| 2019 | 1 |
| D237
| 2019 | 1 |
| D217
| 2019 | 1 |
| B101
| 2019 | 1 |
| D241
| 2020 | 1 |
| D242
| 2020 | 1 |
| D136
| 2020 | 1 |
| D217
| 2020 | 1 |
| B101
| 2020 | 1 |
+------+------+----------+
3.4.
(10 pts) Alter the table
Faculty by adding a column called ‘startDate’ of type
Date.
+-------+----------+----------+----------+---------------------+-----------+
| facId | fname | lname | deptCode |
rank | startDate |
+-------+----------+----------+----------+---------------------+-----------+
| 1011 | Paul | Smith
| CSCI | Professor | NULL |
| 1012 | Mary | Tran
| CSCI | Associate Professor |
NULL |
| 1013 | David | Love
| CSCI | NULL | NULL |
| 1014 | Sharon | Mannes
| CSCI | Assistant Professor |
NULL |
| 1015 | Daniel | Kim
| CINF | Professor | NULL |
| 1016 | Andrew | Byre
| CINF | Associate Professor |
NULL |
| 1017 | Deborah | Gump
| ITEC | Professor | NULL |
| 1018 | Art | Allister | ARTS | Assistant Professor | NULL |
| 1019 | Benjamin | Yu |
ITEC | Lecturer | NULL |
| 1020 | Katrina | Bajaj
| ENGL | Lecturer | NULL |
| 1021 | Jorginlo |
Neymar | ACCT | Assistant Professor | NULL |
+-------+----------+----------+----------+---------------------+-----------+
3.5.
Use two SQL Update commands to achieve the
following:
3.5.1. (5 pts) Set the start date of all faculty members to “2020-08-15”.
+-------+----------+----------+----------+---------------------+------------+
| facId | fname | lname | deptCode |
rank | startDate |
+-------+----------+----------+----------+---------------------+------------+
| 1011 | Paul | Smith
| CSCI | Professor | 2020-08-15 |
| 1012 | Mary | Tran
| CSCI | Associate Professor |
2020-08-15 |
| 1013 | David | Love
| CSCI | NULL | 2020-08-15 |
| 1014 | Sharon | Mannes
| CSCI | Assistant Professor |
2020-08-15 |
| 1015 | Daniel | Kim
| CINF | Professor | 2020-08-15 |
| 1016 | Andrew | Byre
| CINF | Associate Professor |
2020-08-15 |
| 1017 | Deborah | Gump
| ITEC | Professor | 2020-08-15 |
| 1018 | Art | Allister | ARTS | Assistant Professor | 2020-08-15 |
| 1019 | Benjamin | Yu | ITEC | Lecturer | 2020-08-15 |
| 1020 | Katrina | Bajaj
| ENGL | Lecturer | 2020-08-15 |
| 1021 | Jorginlo |
Neymar | ACCT | Assistant Professor | 2020-08-15 |
+-------+----------+----------+----------+---------------------+------------+
3.5.2. (5 pts) Set Paul Smith’s start date to “2000-01-15”.
+-------+----------+----------+----------+---------------------+------------+
| facId | fname | lname | deptCode |
rank | startDate |
+-------+----------+----------+----------+---------------------+------------+
| 1011 | Paul | Smith
| CSCI | Professor | 2000-01-15 |
| 1012 | Mary | Tran
| CSCI | Associate Professor |
2020-08-15 |
| 1013 | David | Love
| CSCI | NULL | 2020-08-15 |
| 1014 | Sharon | Mannes
| CSCI | Assistant Professor |
2020-08-15 |
| 1015 | Daniel | Kim
| CINF | Professor | 2020-08-15 |
| 1016 | Andrew | Byre
| CINF | Associate Professor |
2020-08-15 |
| 1017 | Deborah | Gump
| ITEC | Professor | 2020-08-15 |
| 1018 | Art | Allister | ARTS | Assistant Professor | 2020-08-15 |
| 1019 | Benjamin | Yu | ITEC | Lecturer | 2020-08-15 |
| 1020 | Katrina | Bajaj
| ENGL | Lecturer | 2020-08-15 |
| 1021 | Jorginlo |
Neymar | ACCT | Assistant Professor | 2020-08-15 |
+-------+----------+----------+----------+---------------------+------------+
Go to
the Index
Total:
100 points
This
lab includes exercises for testing embedded
SQL statements in a Python program. You will play the role of an
application developer to build a database application that supports interactions
with users via a command-line user interface.
Instead
of using the SQL language directly, most end users rely on database
applications to benefit from services provided by a database system. Developers
of database applications use embedded SQL statements to connect to a database
server and manipulate data stored on that server; relations returned from the
DBMS are processed or formatted by the database application before being
presented to the end users.
|
Pre-requisites of
running the exercises included in this lab are listed below:
i.
(As in the previous
labs) Being able to connect to the dcm.uhcl.edu MySQL server and use your
own database account to populate your own toyu
database schema. ·
Creating
the toyu database in your own account
ii.
Having access to a
computer where the Python system is properly installed. ·
To run a
Python script on your computer
iii.
Having access to a
computer where the MySQL Python connector is properly installed, and
being able to run a Python program to remotely connect to the dcm.uhcl.edu
database to test embedded SQL
connection and data manipulation. ·
To
run a Python script that accesses a database on dcm |
·
Exercises
a) (20 pts) Repeat
the above steps by accessing your own account and database on dcm.uhcl.edu.
Print relevant screenshots to show that you have completed the pre-requisites
as outlined above (in steps i, ii, and iii).
b) (10 pts) A
sample Python program that handles command-line user input and output is
available on this page: https://stackoverflow.com/questions/71943877/how-to-create-a-menu-system-for-a-console-terminal-application.
For your convenience, the program can be directly downloaded from this file: menu.py
Download
and run the menu.py program and attach a screenshot similar to Figure 1.
Figure 1: Running the menu.py program
c) (10 pts) A
revised menu.py program is available for download: mysqlPythonMenuGlobal.py
That
program demonstrates how a command-line program would take the user’s
choice, trigger an embedded SQL statement to the database server, and process
the returned result. Run the program and attach a screenshot similar to Figure
2.
Figure 2: Running the
mysqlPythonMenuGlobal.py program
d) Revise
mysqlPythonMenuGlobal.py to complete the
following tasks. Run the revised program; hand in the revised program and
relevant screenshots.
Hint:
First, revise the main( ) function. Then
revise the corresponding function for each of the tasks.
NOTE: Always
run option one first to create the database connection, which is then shared by
the other functions. The database connection is closed when the program is
done.
1)
(20
pts) Change ‘option 3’ to ‘showStudentNames’,
which prints only the first name and the last name of each of the students (as
shown in Figure 3).
NOTE: The
result from a cursor consists of multiple items, including the name and value
of each of the columns. A common solution is to treat the returned data as a
dictionary.
Hint: View https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursordict.html
to learn how to print values of selected columns from a cursor.
Figure
3: Showing only the students’ names
2)
(20
pts) Add an option called ‘showAverageNumberOfCredits’,
which prints the average number of credits from the Student relation.
NOTE: View
Figure 4 to see an example of a wrong output format. The average should be
printed as a single value.
Figure
4: Showing the average number of credits of all students
3)
(20
pts) Add an option called ‘showAverageNumberOfCreditsFromMajor’,
which prints the average number of credits for students in a given major. The
major is entered by the user.
Figure
5: Interactive input and output
e) (20 pts) Bonus Project of Lab 3: Submit this together with Lab 3.
Add a function called updateStatus( ), which will do the following:
1. If the column ‘status’ does not exist, add the column to the Student table. Hint: Use ‘alter table’ command to add a new column.
2. Update the ‘status’ column using the logic as shown below:
If credits < 30, then status = ‘Freshman’;
Else if credits < 60, then status = ‘Sophomore’;
Else if credits < 90, then status = ‘Junior’;
Else status = ‘Senior’.
Test the function and submit screen shots and source codes as part of Lab 3.
Go to
the Index