In the first phase, the user requirements are to be collected from your clients. However, you do not have
any clients. Therefore, you need to look for the data you will be managing. Please refer to online
resources such as www.kaggle.com and www.data.gov. You are encouraged to use real dataset for your
project because you will gain more experience than using simulated data. Knowing your data is one of the
most important parts of your design. Note that, we do not want oversimplified databases, but we do not
want incredibly complex databases either. The number of entities in your database should be at least 12
and up to 25. There may be exceptions if your project is innovative.
After Phase 1, the introduction part of your “Project Report” will be ready. It includes an overview about
the database you are designing. Then, you should be able to identify all the requirements, very clearly.
You can create subsections for each part of the database you will be designing, if it gets too complex.
Phase 1 submission: Introduction of your project (Deadline – Sep 15th, 2020):
Team leaders: submit the introduction of your project to iCollege. The TA will provide feedback within
three days. Your project topic must be approved before you proceed to the next phase. If you skip the
approval part, and your project is not satisfactory in the end (meaning that it does not meet the
requirements and standards), points may be deducted from your final project grade.
In the second phase, derive your ER Model (conceptual). Using the requirements that you have identified
in Phase 1, you will first identify the entities, then the attributes of the entities and the relationships among
those entities. Later, the structural constraints of your ER model are to be identified. Please use the (min,
max) notation for cardinality ratio.
Identify each entity and its attributes. Also, identify the primary keys, and state your reasons why you
select that particular primary key. For each of the entities discovered from your requirements, draw a
partial ER diagram, showing the particular entity and its attributes.
Identify the relationships among the entities. Draw a partial ER diagram including the entities that are
participating in the relationships you demonstrate. You can merge the relationships of similar entity types
and show them in one figure. Also, identify the attributes of these relationships (foreign keys and
additional attributes) and state your reasons.
Draw your final ER diagram. There must be at least 12 entity types. You need at least 11 relationships for
connecting these entities. This means all your entities must be related to another entity in the database.
You do not need to include attributes in the final ER diagram, but you must show the minimum and
maximum participation constraints, as well as the participation constraints.
Phase 2 submission: Project Design Report (Deadline – Sep. 29th, 2020)
After you are done with the requirements and your ER model (conceptual schema), it is time to submit
your project design report which will be constituting 20% of your final project grade. The report will be
graded over 20 points. Below you will see four main sections of this report and the distribution of the
● In the first section, briefly describe what the designed database is about (your project topic),
what is your motivation for selecting this project topic, how this database can be used, and some
of the important aspects of your design (2 points).
● The second section is regarding the database requirement analysis. Describe your
requirements (the ones you have identified in Phase 1) one by one (4 points). Additionally,
include specific constraints related to your database. Another important part is the functional
requirements. State at least 10 functional requirements (including modifications and queries) of
your database (4 points). Note that, these functional requirements should be sophisticated (user
login/logout, accessing database, creating tables, modifying table structures, or inserting tuples in
your database are not counted as a functional requirement). You are expected to implement all
the functional requirements in the end.
● The third section is about the ER model. Show the entities, attributes, and relationships one by
one (5 points). Then, in a separate page (as an Appendix), show your final ER diagram, which
does not need to include entity attributes (5 points). Your ER diagram must be in electronic
version, and hand-drawn version is not acceptable. Your ER diagram should follow the style we
learned in class.
● Your report must be professionally written. Do not give very short or long reports. You may use
the provided project design report as a template. You will not get extra credits for being very
professional, but you may lose up to 8 points if your report is not well written.
● For each section of the design report, list the tasks that each team member is responsible for.
If there is a significant difference of workload among team members, your team will be subject to
inspection. You must distribute the workload fairly and reasonably.
● If you work significantly less than your team members, you will not get full credits from this part
of the project. If you are not satisfied with your teammates’ performance/behaviors, you can email
the instructor and TA. You will be notified if we receive a complaint from your teammates.
In the third phase, perform mapping from your conceptual schema (ER Model) to the relational schema.
Some of the mappings may be very tricky. Note that it is always better to map your regular entities, then
the weak entities, and lastly the relationships. Your relational schema must be in electronic version, and
hand-written version is not acceptable.
In Phase 4, perform normalization on your relations and show that your relational model strictly follows
the third normal form. For each relation in your relational model, you must show it is at least in the third
normal form. You will lose points if your relations are not in the third normal form or you do not specify
why they are in the third normal form.
In this phase, create a data dictionary. A data dictionary defines the basic organization of the tables which
are to be created in your database. In a proper data dictionary, the tables, and the attributes (or columns)
of the tables are demonstrated. The relationships among attributes are also shown (foreign key
relationships). Different from the relational model, in a data dictionary, you specify the domain of each
attribute, as well as other constraints that might be important during the implementation phase. Note that
the domain of an attribute includes syntactic and semantic interpretations of an attribute. For example, the
domain for birthdate of an employee is a ‘time’ (or ‘date’, or datetime in some DBMSs). However, you will
only store the day, month, and year values. Similarly, the year for a car can also be stored using
‘datetime’ type objects, but you only store the year. Other example constraints on birthdate could be that
the time cannot be later than the current time instance.
This phase is the implementation phase of your project. It is the fun part of the project. Using your
implementation-ready (normalized), polished relational model, and well-prepared data dictionary, you will
be able to implement your database in the chosen DBMS. You must use the ‘create table’ statement to
create your tables. In this phase, all your primary and foreign key constraints must be mapped from your
relational model. The second part of this phase is to populate your data into the database. To insert data,
you can use a series of ‘insert’ statements or you can use database loading tools, or SQL statements
such as ‘copy’ or ‘load data’. After the insertion of your data, you will implement the functional
requirements as a series of queries. Note that you might even need to create new temporary tables of
nested joins. What you are expected to achieve is to guarantee the correctness of your queries using your
● SQL folder: Create a folder called “SQL”, and upload three files in this directory.
○ The first file should be named as “create.sql” and it must include the DDL commands, such as create
and alter statements. Also, it should include the index creation statements if you have indexes in your
○ The second one should be named as “queries.sql”. In this file, include all the SQL queries which
address the functional requirements you have listed in your report. Comment and format both of the files
properly. If you do not specify which functional requirement a query addresses, it will not be graded.
○ The third file should be named as “insert.sql”, which should have the ‘insert table’ statements. We will
only run “create.sql” and “insert.sql” once (and as a whole), and then check whether your functional
requirements are correctly addressed. Therefore, test all your code and make sure it runs correctly. (An
alternative to the insert.sql is to save a database instance and submit that particular instance. In this
alternative, you must submit a readme file that shows exactly which commands we should run).
● Sources folder: Create a folder called “Sources”. In this directory, you must have all your code and
executables (if necessary) for the Phase 7 work. You should include a readme file along with the code.
The readme file must include which systems (with the versions) are required to run the code as well as
the step-by-step instructions on how to run your code.
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.