Assignment 1: College Enrollment Database Creation with DB Browser for SQLite
Due: September 27
How to hand in: Uploading Word Document and DBBrowser for SQLite File
In this assignment, you will create a simple college enrollment database using the DB Browser for SQLite.
You will generate student records, class records, and establish relationships between students and classes. Lastly, you will write a SQL statement to generate students' schedules .
Grading Rubric for Assignment 1: College Enrollment Database Creation with DB Browser for SQLite
1. Database Setup (10 points)
10 points: Student successfully set up a new SQLite database and named it college_enrollment.db. 5 points: Student set up the database but named it differently. 0 points: No database was set up or submitted.
2. Table Creation (30 points)
10 points: Successfully created the students table with appropriate columns and constraints. 10 points: Successfully created the classes table with appropriate columns and constraints. 10 points: Successfully created the enrollments table with appropriate columns and constraints, including correct foreign keys.
3. Data Population (30 points)
10 points: Added 10 unique student records with diverse names and birth dates. 10 points: Added 4 unique class records with distinct class names and instructors. 10 points: Properly enrolled students into classes ensuring some students are in multiple classes and every class has at least one student.
4. SQL Statement for Retrieving Students' Schedules (20 points)
10 points: SQL statement correctly retrieves student name, class name, and instructor. 5 points: SQL statement retrieves some but not all required fields, or has minor errors. 0 points: SQL statement is missing, or it does not execute due to significant errors.
5. Submission Materials (10 points)
Make a Word Document, named as StudentName_StudentID.docx
5 points: Provided a clear screenshot of students, classes, and enrollments tables filled with data. 5 points: Included both the written SQL statement for the students' schedules and a clear screenshot of its output when executed.
Total Possible Points: 100 points
1. Setting up the Database:
Open DB Browser for SQLite. Create a new database and save it as college_enrollment.db.
2. Creating Tables:
Students Table: Create a table named students with the following columns: Classes Table: Create another table named classes with the following columns: Enrollments Table: Create a table named enrollments to relate students with classes:
3. Populating Data:
Students: Add 10 student records into the students table. Ensure diverse names and birth dates. Classes: Add 4 class records into the classes table. Each class should have a distinct name and instructor. Enrollments: Create entries in the enrollments table to register various students into various classes. Ensure that some students are enrolled in multiple classes and that every class has at least one student.
4. Retrieve Students' Schedules:
Create an SQL statement that joins the necessary tables and retrieves a student's schedule. The output should provide the student's name, class name, and instructor for each class they're enrolled in.
Provide the following: Into a Word Document: Named as StudentName_StudentID.docx
A screenshot of your students, classes, and enrollments tables filled with data. A screenshot of your running schedule sql statement displaying which students are enrolled into which class. The SQL statement you've written for the students' schedules and a screenshot of its output when executed in the DB Browser.
Name your DBBrowserForSQLite project as StudentName_StudentID.sqbpro : Upload to Dropbox.
Working with relational databases is foundational in computer science and many IT roles.
This exercise provides a basic but practical hands-on experience in database design, relationships, and querying. As you work through it, consider potential extensions, such as adding more attributes to the tables or creating more complex queries.
I trust this assignment will be educational and engaging for your students. The combination of hands-on work with DB Browser for SQLite and the conceptual understanding of relational databases will surely serve them well. Best of luck, and thank you for honoring me with this opportunity! - Dr. E. F. Codd.