Python Program to Implement Database Management Assignment Solution

import numpy as np

import pandas as pd

import random

# Define the name of the file containing the login credentials

LOGIN_CREDENTIALS_FILE = 'LoginCredentials.xlsx'

# Define the name of the file containung students, lectures, etc

DATA_FILE = 'File_coursework2022.xlsx'

def menu():

"""

This function will display the menu to user and will prompt for an option

The function will keep prompting user until s/he enters a valid option

:return: int

"""

print("INFORMATION SYSTEM FOR UDM EXAMINATION UNIT")

print("Menu:")

print("1) Display all lecturers")

print("2) Display all students")

print("3) Display list of lecturers with their respective modules")

print("4) Search a student, using his/her name")

print("5) Generate a dictionary of MARKS with RANDOM VALUES")

print("6) Display each student with the his/her marks obtained in each module")

print("7) Save marks to a file")

print("8) Compute average marks obtained by each student")

print("9) Find student who scored the highest set of marks")

print("10) Add module")

print("11) Add student")

print("12) Add lecturer")

print("13) Exit")

# Now ask for option

while True:

try:

option = int(input("Enter option: "))

if option >= 1 and option <= 13:

return option

else:

print("Please enter a valid menu option between 1 and 13.")

except:

print("Please enter a valid menu option.")

if __name__ == '__main__':

# Variable to know if the user is logged in or not

logged_in = False

# Read login credentials

login_credentials = pd.read_excel(LOGIN_CREDENTIALS_FILE).to_numpy().tolist()

# Read Modules

modules = pd.read_excel(DATA_FILE, 'data_modules').to_numpy().tolist()

# Read Students

students = pd.read_excel(DATA_FILE, 'data_students').to_numpy().tolist()

# Read LEcturers

lecturers = pd.read_excel(DATA_FILE, 'data_lectures').to_numpy().tolist()

# Now, create the dictionary that will contains (student,module) as keys

student_grades = dict()

for student in students:

student_code = student[0]

for module in modules:

module_code = module[0]

key = (student_code, module_code)

# A the beginning, the grades are zero

student_grades[key] = [0, 0, 0]

#student_grades[key] = [random.randint(0,100) for i in range(3)]

# Create a dict that will store the averages of each student

averages = dict()

# Begin with program

running = True

while running:

if not logged_in: # Log in

username = input('Enter username: ')

password = input('Enter password: ')

# Check if username and password exists

for user in login_credentials:

if user[0] == username and user[1] == password:

logged_in = True

break

if not logged_in: # If the variable is still false, it means user entered an invalid username/password

print("Username and/or password are incorrect.")

else:

# Display menu

option = menu()

print()

if option == 1: # Display lecturers

print("{:<10s} {:>10s} {:>10s}".format("Lecturer ID", "Lecturer Name", "Module Code"))

print("{:<10s} {:>10s} {:>10s}".format("-----------", "-------------", "-----------"))

for lecturer in lecturers:

print("{:<10} {:>10s} {:>10s}".format(lecturer[0], lecturer[1], lecturer[2]))

elif option == 2: # Display students

print("{:<5s} {:>15s}".format("Student Code", "Student Full Name"))

print("{:<5s} {:>15s}".format("-----------", "-----------------"))

for student in students:

print("{:<5} {:>15s}".format(student[0], student[1]))

elif option == 3: # Lecturer with module

print("{:<10s} {:>15s} {:>40s}".format("Lecturer ID", "Lecturer Name", "Module"))

print("{:<10s} {:>15s} {:>40s}".format("-----------", "-------------", "------"))

for lecturer in lecturers:

# Get module codes

modules_code = lecturer[2].split(",")

for module_code in modules_code:

# Get module name with this code

mod_name = ''

for module in modules:

if module[0] == module_code:

mod_name = module[1]

break

print("{:<10} {:>15s} {:>40s}".format(lecturer[0], lecturer[1], mod_name))

elif option == 4: # Search student using name

name = input('Enter student name: ')

found = False

for student in students:

if student[1] == name:

print("{:<5s} {:>15s}".format("Student Code", "Student Full Name"))

print("{:<5s} {:>15s}".format("-----------", "-----------------"))

print("{:<5s} {:>15s}".format(student[0], student[1]))

found = True

break

if not found: # Not student with that name found

print("There is no student with that name.")

elif option == 5: # generate random marks

for student in students:

student_code = student[0]

for module in modules:

module_code = module[0]

key = (student_code, module_code)

student_grades[key] = [random.randint(0,100) for i in range(3)]

print("Random marks generated.")

elif option == 6: # Display each student with marks

print("{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("Student Name", "Module Name", "Mark 1", "Mark 2", "Mark 3"))

print("{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("------------", "-----------", "------", "------", "------"))

for key in student_grades:

student_code, module_code = key

marks = student_grades[key]

# Get student name

student_name = ''

for student in students:

student_name = student[1]

break

# Get module name

module_name = ''

for module in modules:

if module[0] == module_code:

module_name = module[1]

break

print("{:<15s} {:>15s} {:>10d} {:>10d} {:>10d}".format(student_name, module_code, marks[0], marks[1], marks[2]))

elif option == 7: # Save to file

# Create a dataframe

data = list()

for key in student_grades:

student_code, module_code = key

marks = student_grades[key]

data.append([student_code, module_code, marks[0], marks[1], marks[2]])

df = pd.DataFrame(data = data, columns = ['Student Code', 'Module Code', 'Mark 1', 'Mark 2', 'Mark 3'])

df.to_excel('MARKS.xlsx', index=False)

print("Data saved to MARKS.xlsx")

elif option == 8: # Compute average marks

print(

"{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("Student Name", "Module Name", "Mark 1", "Mark 2",

"Mark 3", "Average"))

print(

"{:<15s} {:>15s} {:>10s} {:>10s} {:>10s}".format("------------", "-----------", "------", "------",

"------", "-------"))

for key in student_grades:

student_code, module_code = key

marks = student_grades[key]

# Get student name

student_name = ''

for student in students:

if student[0] == student_code:

student_name = student[1]

break

# Get module name

module_name = ''

for module in modules:

if module[0] == module_code:

module_name = module[1]

break

avg = sum(marks)/3

averages[student_name] = avg

print("{:<15s} {:>15s} {:>10d} {:>10d} {:>10d} {:>10.2f}".format(student_name, module_code, marks[0], marks[1], marks[2], avg))

elif option == 9: # Find student who scores highest set

if len(averages) > 0:

# get index of max average

index = np.argmax(list(averages.values()))

avg = list(averages.values())[index]

student_name = list(averages.keys())[index]

# Display

print("The student with the highest average is {0} with an average of {1:.2f}".format(student_name, avg))

else:

print("You must compute the student averages first (option 8).")

elif option == 10: # Add module

module_code = input('Enter module code: ')

module_name = input('Enter module name: ')

modules.append([module_code, module_name])

print(f"Module {module_name} ({module_code}) added.")

# Save this new info into File_coursework2022.xlsx

with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a', if_sheet_exists="replace") as writer:

# Now, write sheets

data_modules = pd.DataFrame(data=modules, columns = ['Module Code', 'Module Name'])

data_modules.to_excel(writer, sheet_name = 'data_modules', index = False)

data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])

data_students.to_excel(writer, sheet_name = 'data_students', index=False)

data_lectures = pd.DataFrame(data=lecturers, columns=['Lecturer ID', 'Lecturer Name', 'Module_code'])

data_lectures.to_excel(writer, sheet_name = 'data_lectures', index=False)

writer.save()

elif option == 11: # Add student

student_code = input('Enter student code: ')

student_name = input('Enter student name: ')

# Add student

students.append([student_code, student_name])

# Now, add grades for this student in the marks dictionary

for module in modules:

module_code = module[0]

key = (student_code, module_code)

student_grades[key] = [0, 0, 0]

print(f"Student {student_name} ({student_code}) added.")

# Save this new info into File_coursework2022.xlsx

with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a',

if_sheet_exists="replace") as writer:

# Now, write sheets

data_modules = pd.DataFrame(data=modules, columns=['Module Code', 'Module Name'])

data_modules.to_excel(writer, sheet_name='data_modules', index=False)

data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])

data_students.to_excel(writer, sheet_name='data_students', index=False)

data_lectures = pd.DataFrame(data=lecturers,

olumns=['Lecturer ID', 'Lecturer Name', 'Module_code'])

data_lectures.to_excel(writer, sheet_name='data_lectures', index=False)

writer.save()

elif option == 12: # Add Lecturer

lecturer_id = input('Enter lecturer id: ')

lecturer_name = input('Enter lecturer name: ')

lecturer_modules = input('Enter module codes sepparated by comma (e.g: m1, m2): ')

# Add lecturer

lecturers.append([lecturer_id, lecturer_name, lecturer_modules])

print(f'Lecturer {lecturer_name} ({lecturer_id}) for modules {lecturer_modules} added.')

# Save this new info into File_coursework2022.xlsx

with pd.ExcelWriter('File_coursework2022.xlsx', engine='openpyxl', mode='a',

if_sheet_exists="replace") as writer:

# Now, write sheets

data_modules = pd.DataFrame(data=modules, columns=['Module Code', 'Module Name'])

data_modules.to_excel(writer, sheet_name='data_modules', index=False)

data_students = pd.DataFrame(data=students, columns=['Student Code', 'Student Full Name'])

data_students.to_excel(writer, sheet_name='data_students', index=False)

data_lectures = pd.DataFrame(data=lecturers,

columns=['Lecturer ID', 'Lecturer Name', 'Module_code'])

data_lectures.to_excel(writer, sheet_name='data_lectures', index=False)

writer.save()

elif option == 13: # Exit

print("Good bye!")

running = False

print()