Scribd
Upload
21 views7 pages

Flask Web App Database Model Lab

The assignment requires the creation of a web application using Flask and Flask-SQLAlchemy, along with a specific database model containing three tables: students, courses, and enrollments. Students can be added, updated, and deleted through a web interface, with appropriate HTML forms and tables for displaying data. Submissions must be structured in a specified folder hierarchy and adhere to coding and database guidelines provided in the document.

Uploaded by

24f2006735
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
21 views7 pages

Flask Web App Database Model Lab

The assignment requires the creation of a web application using Flask and Flask-SQLAlchemy, along with a specific database model containing three tables: students, courses, and enrollments. Students can be added, updated, and deleted through a web interface, with appropriate HTML forms and tables for displaying data. Submissions must be structured in a specified folder hierarchy and adhere to coding and database guidelines provided in the document.

Uploaded by

24f2006735
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

BSCCS2003: Week-5 Lab Assignment

In this assignment, you have to create a web application and a database model, using flask and flask-
SQLAlchemy. We list below instructions to be followed in preparing and submitting the solution.
General instructions:
• Submit a single .zip file containing all your submission files and folders, the name of which should be
“<roll number>.zip”. E.g.: [Link]

• The folder structure inside the zip file should be as follows:


– The Python program must be written inside a file named “[Link]”. This file must reside inside
the root folder.
– All the HTML files should be kept inside a folder named “templates” and the images and CSS
files (if any) should be kept in “static” folder. Both the “static” and “templates” folder must
reside in the root directory.
– The database file named “database.sqlite3”. You are not required to submit this database file
with your submission.
• All the endpoints must return 200 as the status code in the case of success.

• You should not keep any code inside the scope of the condition “ if name == ‘ main ’ ” except
run() call.
• Allowed Python packages: jinja2, flask, flask-sqlalchemy, or any standard Python3 package.
• The output pages should be HTML5 compliant, e.g., the file should begin with the declaration
<!DOCTYPE html>.

• Please do not use “create all()” method call in your Python program, and neither add any entry into
the “course” table in your program. You can add those entries outside the Python program (using DB
Browser, terminal etc.).
This is because we are using a different database in the backend with the required course entries
preloaded in it, and using any of the things mentioned above can cause issues during evaluation.
Problem Statement:
• You have to create a database model and must name the database file as database.sqlite3. The database
model must have 3 tables, for which the schema is given below.

• The database URI must be the same as given below:


[Link][’SQLALCHEMY DATABASE URI’] = ‘sqlite:///database.sqlite3’
Note: The table names and column names must be the same as given below.

Column Name Column Type Constraints


student id Integer Primary Key, Auto Increment
Table 1: student roll number String Unique, Not Null
first name String Not Null
last name String
Column Name Column Type Constraints
course id Integer Primary Key, Auto Increment
Table 2: course course code String Unique, Not Null
course name String Not Null
course description String

Column Name Column Type Constraints


enrollment id Integer Primary Key, Auto Increment
Table 3: enrollments
estudent id Integer Foreign Key ([Link] id), Not Null
ecourse id Integer Foreign Key ([Link] id), Not Null

• After creating the model, you have to populate the course table with the following entries (you can
DB Browser or terminal for doing this):

Table: Course
course id course code course name course description
1 CSE01 MAD I Modern Application Development - I
2 CSE02 DBMS Database management Systems
3 CSE03 PDSA Programming, Data Structures and Algorithms using Python
4 BST13 BDM Business Data Management

Using a standard flask template, create an application that:


1. On the home page (URI = ‘/’), (when we open it via the browser) displays an index page. The index
page must display a table with the list of students currently enrolled. The HTML table should be the
same as given below (Its id must be “all-students”). It should display an appropriate message if no
student is enrolled. It should also have a button labeled “Add student”, as shown in the Figure 1.

<table id = "all-students">
<tr>
<th>SNo</th>
<th>Roll Number</th>
<th>First Name</th>
<th>Last Name</th>
<th>Actions</th>
</tr>
<tr>
<td>s_no_1</td>
<td><a href="/student/<int:student_id>">roll_number_1</a></td>
<td>first_name_1</td>
<td>last_name_1</td>
<td>
<a href="/student/<int:student_id>/update" type="button">Update</a>
<a href="/student/<int:student_id>/delete" type="button">Delete</a>
</td>
</tr>
......
......
......
<tr>
<td>s_no_n</td>
<td><a href="/student/<int:student_id>">roll_number_n</a></td>
<td>first_name_n</td>
<td>last_name_n</td>

Page 2
<td>
<a href="/student/<int:student_id>/update" type="button">Update</a>
<a href="/student/<int:student_id>/delete" type="button">Delete</a>
</td>
</tr>

</table>

Note: s no 1, roll number 1, first name 1, last name 1 .......... s no n, roll number n,
first name n, last name n, < int : student id > should be populated accordingly.
2. If the user clicks the “Add student”, your flask application should send a GET request to an endpoint
“/student/create”, which should display an HTML form as shown in the Figure 2. The HTML form
should be the same as given below. Its id must be “create-form”.

<form action="/student/create" method="POST" id="create-form">


<div>
<label>Roll Number:</label>
<input type="text" name="roll" required />
</div>

<div>
<label>First Name:</label>
<input type="text" name="f_name" required />
</div>

<div>
<label>Last Name:</label>
<input type="text" name="l_name" />
</div>

<div>
<label>Select Courses: </label>
<input type="checkbox" name="courses" value="course_1" />
<label>MAD I</label>
<input type="checkbox" name="courses" value="course_2" />
<label>DBMS</label>
<input type="checkbox" name="courses" value="course_3" />
<label>PDSA</label>
<input type="checkbox" name="courses" value="course_4" />
<label>BDM</label>
</div>

<div>
<input type="submit" value = "Submit">
</div>
</form>

• The HTML form should not have any other input elements.
• If the user clicks the submit button, the browser should send a POST request to your flask
application’s “/student/create” URI. The flask application should then create a student object
(with attributes roll number, first name and last name) and enrollments objects(s) (depending on
the number of courses user selects) and add them into the database and, it should redirect to the

Page 3
home page (URI = ‘/’) and the student should be added into the table as shown in the Figure 3.
Note that each row of the table should be clickable
• If the roll number already exists, then, the user should be redirected to an HTML page, which
should display an appropriate message and have a button to navigate back to the home page (URI
= ‘/’), as shown in the Figure 4.

3. If the user clicks the “Update” button, your flask application should send a GET request to an endpoint
“/student/<int:student id>/update”, which should display an HTML form as shown in the Figure 5.
The HTML form should be the same as given below. Its id must be “update-form”.

<form action="/student/<int:student_id>/update" method="POST" id="update-form">


<div>
<label>Roll Number:</label>
<input type="text" name="roll" value="current_roll" disabled />
</div>

<div>
<label>First Name:</label>
<input type="text" name="f_name" value="current_f_name" required />
</div>

<div>
<label>Last Name:</label>
<input type="text" name="l_name" value="current_l_name"/>
</div>

<div>
<label>Select Courses: </label>
<input type="checkbox" name="courses" value="course_1" />
<label>MAD I</label>
<input type="checkbox" name="courses" value="course_2" />
<label>DBMS</label>
<input type="checkbox" name="courses" value="course_3" />
<label>PDSA</label>
<input type="checkbox" name="courses" value="course_4" />
<label>BDM</label>
</div>

<div>
<input type="submit" value = "Submit">
</div>
</form>

Note: < int : student id >, current f name, current l name and current roll should be
populated accordingly.
• The HTML form should not have any other input elements.
• If the user clicks the submit button, the browser should send a POST request to your flask
application’s “/student/<int:student id>/update” URI.
• The flask application should then update the student and corresponding enrollments into the
database and redirect to the home page (URI = ‘/’).

Page 4
4. If the user clicks the “Delete” button, your flask application should send a GET request to an end-
point “/student/<int:student id>/delete”, which should delete the student and all the corresponding
enrollments from the database and redirect to the home page (URI = ‘/’).
5. If the user clicks on the roll number of any row in the table in the home page of the flask application,
the application should send a GET request to an endpoint “/student/<int:student id>”, which should
show all the information (student details and enrollment details) in an HTML page. The HTML page
should also have a button labelled “Go Back” to navigate back to the home page (URI = ‘/’), as shown
in the Figure 6. There must be 2 HTML tables in this page, one for showing the personal details and
the other for displaying the enrollment details. The HTML for showing personal details should be the
same as given below. Its id must be “personal-details”.

<table id = "personal-details">
<tr>
<th>Roll Number</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
<tr>
<td>roll-no-of-student</td>
<td>first-name-of-student</td>
<td>last-name-of-student</td>
</tr>
</table>

Note: All the <td> should be populated accordingly.


The HTML for displaying the enrollment details should be the same as given below. Its id should be
“enroll-table”.

<table id = "enroll-table">
<tr>
<th>S. No.</th>
<th>Course Code</th>
<th>Course Name</th>
<th>Course Description</th>
</tr>
<tr>
<td>1</td>
<td>course-code-for-course_1</td>
<td>course-name-for-course_1</td>
<td>course-description-for-course_1</td>
</tr>
......
......
......
<tr>
<td>2</td>
<td>course-code-for-course_n</td>
<td>course-name-for-course_n</td>
<td>course-description-for-course_n</td>
</tr>
</table>

Note: All the <td> should be populated accordingly.

Page 5
Figure 1: Home

Figure 2: Add Student Form

Figure 3: Home

Page 6
Figure 4: Already Exists Page

Figure 5: Update Student Form

Figure 6: Student Details Page

Page 7

Common questions

Powered by AI

The key functional requirements include displaying a list of students on the home page with an option to add students, populate course data using forms, handle student creation and updating through specified URIs, and maintain student-course enrollments. The system should also gracefully handle errors, such as duplicate roll numbers, by redirecting users to error pages explaining the issue .

Designing separate tables for personal and enrollment details ensures clarity and ease of information access on the student details page. By segregating student attributes from course enrollments, users can quickly understand the scope of information presented and navigate between personal details and course data efficiently, leading to a better user experience and quicker decision-making processes .

The document specifies creating well-defined HTML forms with mandatory fields for roll number, first name, and last name, and checkbox inputs for course selections. This design enforces data type and structural integrity. The form data is submitted as a POST request, which the application processes to create student and enrollment records in the database, ensuring systematic and error-free data capture during student enrollment .

In the described web application, GET requests are utilized for retrieving and displaying data, such as when loading forms for creating or updating student records or when fetching the student details page. POST requests, on the other hand, are used to submit data to the server, such as when creating a new student entry or updating existing student information. This separation of responsibilities aids in maintaining clarity and organization within server-client interactions .

When encountering a duplicated roll number during student creation, the application redirects users to a specific HTML page that displays an appropriate error message. This error page includes a button for navigating back to the home page, ensuring that users are promptly informed about the duplication and have a clear path to start over or review existing entries without confusion .

Updating a student's information involves clicking the 'Update' button, which sends a GET request to '/student/<int:student_id>/update' to display a form pre-populated with current data, except for the roll number which is disabled. Upon submission of this form via POST request, the application updates the student's record and associated enrollments in the database, then redirects back to the home page, reflecting the updated information in the student list .

Disallowing the 'create_all()' method ensures that the database schema remains consistent and compatible with a pre-configured backend that contains the necessary course entries. This prevents accidental overwriting or modification of database tables, thus ensuring that all evaluations reference a standardized dataset, which is crucial for maintaining a controlled development environment .

Data integrity and consistency within the database model are ensured through the use of primary and foreign keys alongside constraints. For example, the 'student' table uses 'student_id' as a primary key which is auto-incremented, ensuring uniqueness. The 'roll_number' in the 'student' table and 'course_code' in the 'course' table are unique and cannot be null, preventing duplicate or incomplete entries. Foreign keys link the 'enrollments' table to the 'student' and 'course' tables, enforcing relational integrity across tables .

Key design considerations include maintaining a consistent form layout with clearly labeled fields, ensuring required fields are marked and validated, and using intuitive input mechanisms such as checkboxes for course selection. For the 'Update student' form, pre-filling current student data helps maintain consistency and reduces user input errors. Providing direct means to submit data and intuitive navigation options enhances user experience and usability .

The architectural considerations for structuring a web application using Flask and Flask-SQLAlchemy as described include maintaining a clear folder hierarchy: the root folder should contain 'app.py', a 'templates' folder for HTML files, and a 'static' folder for CSS and images. The database model must be designed with precise table schemas, including primary keys and foreign keys with specific constraints. Each HTTP endpoint must reliably return a 200 status code upon success, and the use of libraries is limited to Flask, Flask-SQLAlchemy, and standard Python packages to ensure consistency and simplicity of the application .

You might also like