Assignment -

Overview - This assignment covers applied database design and the use of database interfaces in a high level programming language (Python 3). Your submission for this assignment will consist of a database definition (CREATE statements and accompanying constraint definitions) in SQL (submitted as a .txt file due to conneX limitations) and a set of short Python 3 programs to act as database  front- ends. Some of the Python programs will read comma-separated input data and perform database insertion and update operations. The other programs will use SELECT statements to retrieve data from the DBMS and format it as a report for the user. This assignment is a small-scale example of the type of database interaction that you might normally encounter as a developer.

Although this assignment requires writing Python code, the core objective of the assignment is to write as little   Python as possible: the Python programs you write should function as simple appliances to convert input data INSERT or UPDATE statements and convert query results to formatted text. For full marks, you will be expected to leave all of the processing logic (checking the validity of data, moderating conflicts which arise in the data entry, processing results, etc.) to the DBMS and perform no non-trivial data processing in your Python code.

Since you may not have used Python recently, some example programs have been posted which demonstrate Python's capabilities for reading comma-separated data and producing formatted output. You may also find the examples of the psycopg2 interface from the lectures to be useful in designing your solution.

Since your programs will contain very little non-trivial code, it should suffice to use the installed psycopg2 module (for PostgreSQL connectivity) and the Python standard library, but you are free to use any other modules that may be available.

You are required to use Python 3 for this assignment (if you use Python 2, or any other language, your submission will not be marked).

The Task: A Student Registration Database

Your task for this assignment is to design a database schema and a set of front-end programs for a student registration database (similar to several example databases we have seen over the semester, as well as the larger-scale database actually used at UVic). Your database will track students, the courses they take and the grades they receive.

You will submit the files below. They must be named as shown.

• create\_schema.txt: A schema creation file, consisting of an SQL script with DROP and CREATE statements for each table, along with DROP and CREATE statements for all required constraints. It must be possible to completely drop/recreate your database schema by running all of the commands in this file.
• add_drop.py
• assign_grades.py
• create_courses.py
• report_classlist.py
• report_enrollment.py
• report_transcript.py

For this assignment, your database will focus on students, courses (and course offerings) and grades. Other information (like staffing and accounting) has been omitted, but would normally be present in such a database. The subsections below detail the requirements and restrictions of each facet. You are not required to use any particular data model for your database, but you are encouraged to start by sketching an E/R diagram for the requirements (the exact set of entities may vary, but it will likely include separate entities for students, courses, course offerings,  enrollments and grades). Requirements which are marked by an asterisk (*) in the sections below are deliberately unrealistic and are present to help simplify the task for this assignment.

Attachment:- Assignment Files.rar