COMPSCI 351 : Fundamentals of Database Systems S1 C - Lab 02: MySQL DBMS
COMPSCI 351 S1 C – Lab 02
- In Lab01 question 3, we used the PhpMyAdmin tool as an interface for accessing the MySQL DBMS back end. In this question, we use a programming interface to directly access the created databases of the MySQL DBMS.
MySQL Connector for Python (https://dev.mysql.com/doc/connector-python/en/) provides a database connectivity mechanism for communicating with MySQL servers through a Python program. Please read the above page link and install the ‘mysql.connector’ on your Python3 runtime environment. The following is a simplified code segment that enables the login to your MySQL account and display the names of all databases.
import mysql.connector
from getpass import getpass
from mysql.connector import connect, Error
def dbexec(conn, query):
with conn.cursor() as cursor:
cursor.execute(query)
for tb in cursor:
print(tb)
def main():
try:
with connect(
host="fosmysqlprd01.its.auckland.ac.nz",
user=input("Enter username: "),
password=getpass("Enter password: "),
) as connection:
print('DATABASE NAMES')
dbexec(connection, "SHOW DATABASES")
# extend your code here
except Error as e:
print(e)
main()Your task is extending the above program in order to achieve the additional functions below: • Allow the user to select a database and display its tables. The DML statements are ‘USE database_name' and 'SHOW TABLES'. Note that database_name should be replaced with an actual database name. • Allow the user to select a table and display its content. The DML statement is 'SELECT * FROM table_name'. Note that table_name should be replaced with an actual table name. • Allow the user to enter a DML query statement and display the result of the query.
You can download the “Lab02_Q1_code.py” file and extend it. Since the MySQL DBMS is hosted inside the University intranet, the access from a programming interface can only be established via UOA network, i.e., either LAN on campus or remotely using VPN connection. A sample output of the completed program is shown as follows. Note that ‘abcd001’ is used as the example login UPI. You should replace it with your own student UPI value.
Enter username: abcd001
Enter password:
DATABASE NAMES
('information_schema',)
('performance_schema',)
('teaching_',)
('teaching_abcd001_A1',)
('teaching_abcd001_COMPANY',)
('teaching_abcd001_UNIVERSITY',)
('teaching_abcd001_Lab01',)
Select a database: teaching_abcd001_lab01
('COURSE',)
('GRADE_REPORT',)
('PREREQUISITE',)
('SECTION',)
('STUDENT',)
Select a table to show content: SECTION
(85, 'MATH2410', 'Fall', '07', 'King')
(92, 'CS1310', 'Fall', '07', 'Anderson')
(102, 'CS3320', 'Spring', '08', 'Knuth')
(112, 'MATH2410', 'Fall', '08', 'Chang')
(119, 'CS1310', 'Fall', '08', 'Anderson')
(135, 'CS3380', 'Fall', '08', 'Stone')
Enter a query statement: SELECT Course_name, Course_number,
Credit_hours FROM COURSE WHERE Department='CS'
('Intro to Computer Science', 'CS1310', 4)
('Data Structures', 'CS3320', 4)
('Database', 'CS3380', 3)[6 marks]
- Composite and multi-valued attributes can be nested to any number of levels. Suppose we want to design an attribute for a STUDENT entity type to keep track of previous college education. Such an attribute will have one entry for each college previously attended, and this entry is composed of: college name, start and end dates, degree entries (degrees awarded at that college, if any), and transcript entries (courses completed at that college, if any). Each degree entry is formed of degree name and the month and year that it was awarded, and each transcript entry is formed of a course name, semester, year, and grade. Design such an attribute to hold this information using the ER diagram notation. [6 marks]
