Database Systems: Lab1

There are two questions in this Lab.

The first question is giving four different relations and writing the SQL according to the relations.

The second question is giving the relation schema and creating the relation in the real database, and finally execution the SQL which question gives.

The data in Excel can click here:（The link will open after lab）

First Question

Create Tables and Insert Data

The lab gives four pictures about different relations, so the first we should do is create a table according to the picture.

However, due to the abundance of data(maybe not too much? haha, I’m too lazy), we utilize Python to assist us in creating tables.

More about MySQL in Python you can click here: MySQL：Execute SQL By Programming

import pymysql
import pandas as pd

db = pymysql.connect(host='localhost', user='root', port=3306,
                     password='123123', database='bulbul')

cursor = db.cursor()

data = pd.read_excel("./database-systems-lab1.xlsx", sheet_name=None)

sql = "SHOW TABLES LIKE %s"

cursor.execute(sql, "Product")
if(len(cursor.fetchall()) == 0):
    creat_table = "CREATE TABLE Product(maker CHAR(1),model INTEGER,type CHAR(10));"
    cursor.execute(creat_table)
    db.commit()

cursor.execute(sql, "PC")
if(len(cursor.fetchall()) == 0):
    creat_table = "CREATE TABLE PC(model INTEGER,speed FLOAT,ram INTEGER,hd INTEGER,price FLOAT);"
    cursor.execute(creat_table)
    db.commit()

cursor.execute(sql, "Laptop")
if(len(cursor.fetchall()) == 0):
    creat_table = "CREATE TABLE Laptop(model INTEGER,speed FLOAT,ram INTEGER,hd INTEGER,screen FLOAT,price FLOAT)"
    cursor.execute(creat_table)
    db.commit()

cursor.execute(sql, "Printer")
if(len(cursor.fetchall()) == 0):
    creat_table = "CREATE TABLE Printer(model INTEGER,color CHAR(5),type CHAR(10),price FLOAT)"
    cursor.execute(creat_table)
    db.commit()

relationList = ["Product", "PC", "Laptop", "Printer"]

for relation in relationList:
    insert_value = "INSERT INTO "+relation+" VALUES (%s"
    temp = data[relation]
    for col in range(len(temp.columns.tolist())-1):
        insert_value += ",%s"
    insert_value += ");"
    for i in range(len(temp)):
        print(insert_value)
        value_list = temp.loc[i].values.tolist()
        # value_list.insert(0, relation)
        cursor.execute(insert_value, value_list)
        db.commit()
    cursor.execute("SELECT * FROM "+relation)
    print(cursor.fetchall())

cursor.close()
db.close()

# SDUT Database Systems Lab1: Create table
# 2023 Sep. in SDUT
# By Bulbul

Check the operation:

Answer the Question

a) What PC models have a speed of at least 3.00?
b) Which manufacturers make laptops with a hard disk of at least 100GB?
c) Find the model number of all color laser printer
d) Please Use two INSERT statements, store in the database the fact that PC model 1100 is made by manufacturer C, has speed 1800, RAM 256, hard disk 80, and sells for $2499.
e) Manufacturer A buys manufacturer B. Change all products made by B so they are now made by A.

Five questions here, the SQL statements we used are:

1. SELECT
2. INSERT INTO
3. UPDATE

Second Question

In this question, we should following the schema which it provide to create tables.

Movie(title, year, length, filmType, studioName, producerC#)
Exec(name, address, cert#, netWorth)

Create Tables and Insert Data

In this question, we use the command line to create tables, and use Python to insert data.

CREATE TABLE Movie(title CHAR(50),
                    year INTEGER, 
                    length INTEGER, 
                    filmType CHAR(10), 
                    studioName CHAR(30), 
                    `producerC#` INTEGER);
CREATE TABLE Exec(name CHAR(30), 
                    address CHAR(50), 
                    `cert#` INTEGER, 
                    netWorth DOUBLE)

import pymysql
import pandas as pd

db = pymysql.connect(host='localhost', user='root', port=3306,
                     password='123123', database='bulbul')

cursor = db.cursor()

data = pd.read_excel("./database-systems-lab1.xlsx", sheet_name=None)

relationList = ["Movie", "Exec"]

for relation in relationList:
    insert_value = "INSERT INTO "+relation+" VALUES (%s"

    temp = data[relation]
    for col in range(len(temp.columns.tolist())-1):
        insert_value += ",%s"
    insert_value += ");"
    for i in range(len(temp)):
        value_list = temp.loc[i].values.tolist()
        # value_list.insert(0, relation)
        print(value_list)
        cursor.execute(insert_value, value_list)
        db.commit()
    cursor.execute("SELECT * FROM "+relation)
    print(cursor.fetchall())

cursor.close()
db.close()

Answer the Question

The difference between the queries is whether use the HAVING.

1. Only WHERE without HAVING:
   • The data will be filtrated first, and grouped second.
2. Both WHERE and HAVING:
   • The data will be grouped first, and filtrated second.