CSE4560-Project 1 Solved

CSE 4/560 Project1: TinyHub - Part 3


1           Description
You are to design and implement database schema for TinyHub, which is a course enrollment system. It provides simple functions, main functions for TinyHub are the following:

•    User management

•    Department management

•    Course management

•    Student Course management

Your DB schema must be able to support all the functions listed above. You are required to use E/R modeling (an online E/R diagram tool is https: //www.draw.io/) to design and present your database, and map your E/R model to a relational database schema and implement the mapped schema using a RDBMS by a set of CREATE TABLE statements. A recommended RDBMS is:

•    MySQL(https://dev.mysql.com/downloads/): you need to first install MySQL Community Server to use MySQL, then you’ll need one of MySQL Shell and MySQL WorkBench for SQL development. If you need a GUI, you may prefer to use MySQL WorkBench.

You also need to briefly explain how your model supports all the required functions. The final product of this is a report (.pdf file), of which the structure will be given in section 3 in this description, and a SQL file (.sql file type) which contains all the CREATE TABLE statements you used for implementing your RDB schema.


2           Requirements of functions
The specific requirements for each function of TinyHub are given in this section. Note that you need to analyze and design the entity types, attributes and relationship types, while this project description only gives some of them for clarifying the system requirements.

2.1         User management
1.    Users can be of three types: Students, Professors, or Staff.

2.    Users sign up their accounts with their email addresses.

3.    Users need to set their passwords.

4.    Account, password, and display names are strings.

5.    One account has an optional unique display name. Users are allowed to change their display name as long as it is unique.

6.    One email address can be used to register only one account.

2.2         Department management
1.    Each department has an identifier department number.

2.    Every professor is hired by one department.

3.    Every staff is hired by one department.

4.    Departments offer different programs, which a program can only belong to one department.

5.    Students are allowed to major in different departments.

6.    Students can pursue different programs, but they must major in the department which is offering that program. e.g. Given a program ”Data Science” which is offered by CSE department, then only students major in CSE are allowed to

2.3         Course management
1.    Each department offers different courses.

2.    A course can be opened in different semesters.

3.    A semester is identified by a year and a season, e.g. 2020 Spring.

4.    A course does NOT have to be opened every semester.

5.    Every course has TAs, and one instructor. However, it is possible to change TAs or the instructor even during the semester.

6.    An instructor must be a professor.

7.    A TA must be a student.

8.    A course may contain prerequisite courses.

9.    A student may register in different semesters.

2.4         Student-Course management
1.    A student can enroll different courses. But that student can enroll a course only if

•    That student has registered in the semester and the course is offered in that semester. i.e. A student need to register 2020 Spring before enrolling any course in 2020 Spring.

•    That student passes all the prerequisite courses

•    It is being offered by a department they are majoring in

•    The capacity of the course is not full

2.    Students will have a grade (F/D/C/B/A) with the course, which will be given after the student finishes the course.

3.    Students can post feedback for the instructor of the course in which they enrolled.

4.    Each course has one or more exams. Students who take that course have letter grades on those exams.

5.    Each exam has a number of problems. Students have scores on those problems.

3           Report template
Here’s a template of the report, your report must contain each of the following sections, you can extend this template to include any necessary sections for your design.

•    E/R schema: in this section you need to introduce your E/R schema and include the picture of your E/R schema.

•    Relational database schema: in this section, you must

–    discuss briefly how you map the E/R schema to your relational database schema. If any design choice is made in the mapping process, illustrate and explain it briefly.

–    discuss briefly how your relational database schema satisfies all the requirements listed in section 2.

•    Further discussion: in this section, you need to discuss the advantages and disadvantages of your design.