Sonoma State University
Department of Computer Science
CS-370: Software Design and Development
Assignment: Database Schema

OBJECTIVE

  • Create a MySQL relational database schema to hold all data relevant for an online auction website.
  • The database schema should indicate the primary keys in each table.
  • The schema should be structured in Third Normal Form (3NF) with no duplication of data spanning multiple tables.
  • You may hand-draw your schema with paper (then upload a digital scan of the paper) or use illustration software to create a schema.
Example database schema:

The following image is a real-world, illustrated example of a database schema I created in third normal form. I designed this database schema for Postgres (another SQL-based relational database management system like MySQL). The "BIGSERIAL" datatype is proprietary to Postgres and serves the same purpose as MySQL's "LAST_INSERT_ID()" functionality for atomically generating a unique non-negative integer as a primary key. The "BYTEA" datatype (yes, that's not a typographical error) is proprietary to Postgres and is equivalent to ANSI SQL as a BLOB (Binary Large OBject) of data. Each series of tables is comprised of one or more columns. The top column in each table (teal-colored) represents the name of a database table. The purple highlighted column(s) within each table represent(s) the primary key for that table. The remaining columns represent other data structures contained within that database table. The arrow lines tie the tables together through the primary key (or a foreign key). This is called a relation. When a relational database merges the data from two tables together based on a common field, we call that a "join" in SQL-lingo.

Uploading your database schema file

  • Please upload your database schema in either PDF document or as a PNG image.

Database Schema Rubric

CRITERIA RATINGS POINTS
Third Normal Form:
Is the database structure in third-normal form?		 Excellent
5 points
The schema is modular with no duplication of data. Each table in the schema contains a primary key. Each table in the schema may also contain a foreign key.		 Satisfactory
3.5 points
The database schema is modular but contains duplicate data spanning multiple tables. Each table has a primary key.		 Needs Improvement
1.67 points
The database schema is incomplete or disorganized. One or more tables lacks a primary key. There is duplication of data spanning multiple tables. There doesn't appear to be a logical connection for how the data is organized.		 Unsatisfactory
0 points
No database schema submitted.		 5 points
Table names:
Are the names of each table descriptive of the type of data being stored?		 Excellent
2 points
The database schema contains descriptive names for every table. These descriptive table names are indicative of the type of data that the table holds. The table names are relevant to an auction website.		 Satisfactory
1.4 points
One or two tables in the database schema contains esoteric naming conventions which are non-descriptive of the type of data the tables represent; OR the naming conventions for two or more tables seems irrelevant to an auction-based website.		 Unsatisfactory
0 points
Table names are non-existent; OR three or more table names are non-descriptive. It is not clear what type of data these table(s) are meant to store. The table names may be irrelevant to an online auction website.		 2 points
Column names:
Are the names of each column (field) within each table of the database schema descriptive of the type of data being stored in that column?		 Proficient
1 point
All the field names (columns) are descriptive of the type of data these fields hold and represent. The field names are relevant to an online auction website.		 Satisfactory
0.7 points
One or two columns in the whole database schema are non-descriptive of the type of data the columns represent and do not appear relevant to an online auction website.		 Unsatisfactory
0 points
Column names are non-existent; OR three or more columns in the database schema contain esoteric naming conventions which are non-descriptive of the type of data such tables represent; OR three or more columns appear to be irrelevant to support the functionality for an online auction website.		 2 points
ANSI SQL datatypes:
Are valid ANSI SQL datatypes or MySQL specific datatypes defined for each column of data?		 Proficient
1 point
Every field is defined by either an ANSI (American National Standards Institute) SQL standard datatype OR a proprietary MySQL datatype.		 Satisfactory
0.75 points
One or two columns in the whole database schema use datatypes that do not exist in ANSI SQL or MySQL.		 Unsatisfactory
0 points
Columns with the database schema do not exist; OR three or more columns are defined by non-existent ANSI SQL datatypes; OR three or more columns are defined by non-existent MySQL datatypes.		 1 point
Total points: 10