Fall 2021 EECS 3421 1
LE/EECS 3421 – Introduction to Database Systems
Fall 2021 (Sections A & B)
Programming Assignment 1
Submission Deadline: October 24, 2021 before 23:59
Assignment 1: Database Design
Objectives
The purpose of this activity is to practice the fundamentals of Database Design using ERDs, Relational Data Model
and SQL.
Submission Requirements
• Please submit your results/output in a PDF file. Submit your code in a text (.txt) file.
• The name of the files must be your YorkU student number such as 100131001.pdf or 100131001.pdf.
• File must be uploaded to the A01 submission link provided. The name of submission on the link must also be
your YorkU student number.
• Your diagrams can be hand drawn or digital. In both cases, the images must be clear.
• If the files do not open properly or the content is not clear, then you will be awarded zero.
• Deadline is Sun. October 24, 2021 before 23:59. Late submission is not permitted under any
circumstances.
• This is an individual assignment.
• Your submissions will be verified using Turnitin (or some other suitable tool) for originality. 60% or
more similarity will be awarded zero in the assignment and reported to the department. We may report
similarity less than 60% if it is of significant nature.
Note: In database design, variations are always possible. If the business requirements are taken care of in a
proper logical way as per the data modeling guidelines, the answer will be considered correct.
Warning: Please add the necessary headings and labels to your report so that the TAs can understand the
different parts properly. Anything that we cannot understand will be awarded zero.
General Instructions
• Please make sure that your document is easy to understand; clearly add the question number, part
number, captions and foot notes wherever required. If the TA can’t locate the answer, then it is your
responsibility.
• Make sure that images/screenshots are clear. If the image is big then split it into multiple parts. Clearly
write their purpose. You can add multiple images even if the question statement doesn’t say so to make
sure that your answer is easy to comprehend.
• Highlight the significant parts of each image so that TA can easily identify the required answer.
• Add necessary explanation to make sure that TA can understand different parts of your document.
Fall 2021 EECS 3421 2
Question 1: ER Modeling [5 Marks]
Solve Q 4.17 from the book and add the answer to your pdf submission file. Below is the statement for
your reference. You need to draw/design only the additions/modifications. Clearly indicate what changes
you introduced and their purpose. The figure number mentioned in the problem statement is wrong
and the bank schema is given in figure 3.22.:
Question 2: Reverse Engineering: Relational Model to ER Model [10 Marks]
Call for Papers or CFP is a popular term in academic research. Fundamentally, it is an announcement for
the researchers to submit their research articles for upcoming conferences, journals or books. The CFPs
can be classified into these three categories, for: Conferences, Journals or Books.
We want to design a database to record all the CFPs. Below are the links for three events for your
understanding (one example from each kind of CFP):
• Conference CFP: http://cs-conferences.acadiau.ca/ant-21/#callforPapers
• Journal CFP: https://www.journals.elsevier.com/journal-of-information-security-andapplications/call-for-papers/special-issue-multimedia-forensics-and-data-hiding-recent-advances
• Book CFP: See email in Appendix A
For this system, consider the Relational Model given in the form of SQL code/script in the file
a01script.sql. Draw an ER Diagram that represents this Relational schema and add the diagram to your
submission document.
Question 3: Creating Schema [4 + 2 + 6 Marks]
Use the SQL script (a01script.sql) provided with the assignment to prepare the database schema. Before
you begin, you must make sure that you don’t have schema objects with the same names already in your
database. Furthermore, you have to make the following additions to the schema provided in this script:
• Create an additional summary table “CFPCount” which stores the count of CFPs of each type.
Also, write a suitable trigger to update this table “CFPCount” whenever a new CFP row/entry is
inserted or an existing is deleted. The details of this implementation are up to your imagination; as
long as it fulfills the purpose, your solution will be considered correct.
You are required to:
• Submit all the code for schema creation, after your additions, in a script file. Name this file as
yourID_q3.sql.txt.
• Run this script on your MySQL server and add the screenshots. Your screenshots must:
Fall 2021 EECS 3421 3
o Show the list of tables (there should be none) before the script is executed; use some
suitable technique to do that.
o Show the statement that executes the script and the success status of the script.
o Show the list of tables and other schema objects after the script is executed.
o The whole script must execute successfully.
Question 4: Scraping and Inserting Data [2 + 6 Marks]
Our next problem in this assignment is to extract the data from the CFP announcements and then insert it
into the Database using the suitable INSERT statements. Each student must process at least ONE CFP
announcement and create the INSERT statements for this CFPs. Select ONE (or more from the
additional file in case you are interested to insert more data) CFP announcement(s) from your designated
file (Appendix C), scrape the data from the CFP(s) (use the method described in Appendix B) and prepare
the INSERT statements accordingly. While scraping the data, you are not required to be precise;
make sure you process some meaningful information. At least 1 Topic, 1 People and 1 Activity must
be inserted for a CFP. You are required to:
• Submit all the code in a script file. Name this file as yourID_q4.sql.txt.
• Run this script on MySQL server and add the screenshots. Your screenshots must:
o Show the statement to execute script and the success status of script.
o The whole script must execute successfully.
[TCCC-ANNOUNCE] Handbook on Blockchain: CALL FOR CHAPTERS
1 message
D. A. T.