Toyu Database (Toy University DB)

Toyu is a drastically simplified toy university database adapted from Ricardo (textbook). It is used in lectures, classroom demonstrations, the first SQL homework assignment, the mid-term examination, and the final examination. Toyu will also be used in Mongodb's lectures and assignments.

1. Accessing Toyu DB

1.1 Installing Toyu DB in your computer

Installing MySQL:

1. XAMPP: contains MariaDB (close to MySQL): https://www.apachefriends.org/
   1. Include other server software, e.g., Apache (the Web server needed in a Python-Web application assignment)
   2. Include MySQL command line client (mysql console/prompt)
   3. Does not include MySQL Workbench (a MySQL client)
2. MySQL: https://www.mysql.com/
   1. Include MySQL command line client
   2. Include MySQL Workbench

See more about setting up MySQL server and client at firstSQL.html.

Creating and populating toyu:

MySQL creation script for creating toyu: Createtoyu.sql.txt (remove .txt while saving). You can execute it with the source command in MySQL command line prompt:

1. Save the script file as createtoyu.sql in your working directory for this class.
2. Open a command line terminal in your working directory.
3. Start mysql command prompt: "mysql -u your_local_mysql_account -p"
4. Run "source createtoyu.sql" within mysql command prompt.

1.2 Remote access to toyu

• The toyu database has been created in the MySQL server in the departmental DCM server: dcm.uhcl.edu.
• The MySQL account "dbguest" is created for experimentation and HW development at the DCM server. The account has only read privilege on two databases: toyu and swim. The password will be revealed in the class or through email.
• You may connect to it using the host dcm.uhcl.edu and the account dbguest.

mysql -h dcm.uhcl.edu -u dbguest -p

2. Toyu Documentation

2.3 Overview and Description

Toyu is a very drastically simplified database to support a toy university application. The goal is to make it extremely small so it can be used in our classes.

Students in toyu has unique student id. Their first names, last names, and accumulated credit hours (ach) should be stored.

There are departments or programs, such as Computer Science (CSCI). A department has a unique department code of four characters. It also has a name. The number of staff of a department should be stored. A student can have a major and a minor, which are programs represented by the department code.

A department is housed under a school. A school contains a unique code (e.g. CSE) and a unique name (e.g. 'College of Science and Engineering').

Faculty members are hired to work under a single department. Toyu stores a unique id, name, and rank of every faculty.

Toyu offers courses. A course has a unique id, rubric, number, title, and credit. A course is offered by a department so that the rubric of a course is the department code of the offering department. A class is an offering of a course in a semester. The semester, year, room, and the instructor should be recorded. An instructor is a faculty member.

Students enroll in classes. The grade and the number of alerts of each class enrollment is recorded. Grades are defined in toyu with a grade point. For example, the grade 'A' has a grade point of 4.0.

2.2 Simple schema and keys

Relations/tables (primary key underscored):

Grade(grade, gradePoint)
School(schoolCode, schoolName)
Department(deptCode, deptName, schoolCode, numStaff)
Faculty(facId, fname, lname, deptCode, rank)
Course(courseId, rubric, number, title, credits)
Class(classId, courseId, semester, year, facId, room)
Student(StuId, fname, lname, major, minor, ach, advisor)
Enroll(stuId, classId, grade, n_alerts)

Foreign keys:

1. Student(advisor) references Faculty(facId)
2. Student(major) references Department(deptCode)
3. Student(minor) references Department(deptCode)
4. Faculty(deptCode) references Department(deptCode)
5. Department(schoolCode) references School(schoolCode)
6. Enroll(stuId) references Student(stuId)
7. Enroll(classId) references Class(classId)
8. Enroll(grade) references Grade(grade)
9. Class(courseId) references Course(courseId)
10. Class(facId) references Faculty(facId)
11. Course(Rubric) references Department(deptCode)

toyu_relations.pdf:

• Contains the relation schema and instance of toyu.
• Will be provided in the mid-term and final examination.

toyu_schema.pdf:

• Contains a more detailed description of the relation schema of toyu, including candidate keys, foreign keys, nullable and non-nullable attributes/columns.
• Includes functional dependencies and the highest normal form of every table.

2.3 Models and Diagrams

toyu_er_workbench.jpg:

• An Entity-Relationship (ER) diagram created using MySQL Workbench.
• A rectangle is a relation.
• In other versions of ER diagrams, a rectangle is an entity (similar to a class).

toyu_uml.jpg: an UML diagram created by Astah.

toyu.asta: the UML model in Astah file format.

toyu_access_relationship_diagram.jpg:

• The relationship diagram (relationship between tables) in MS Access version of toyu (toyu.accdb) is shown below.
• Each rectange is a relation, not a class.
• Relationships are usually between foreign keys and the referenced primary keys.

3. MongoDB

Toyu is also used in MongoDB in this class. See: IntroMongo.html.