CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 1
CS1F IM Practical 4 (Lab) Distributed Week 5

CS1F IM Practical 4 (Lab):
Populating & Querying Your Database
1 Background
So far you have:
(1) created an ER diagram of the database in Tutorial 1.
(2) and then you produced a relational database in MySQL Workbench to hold data that
conforms to the ER specification.

This week you will populate the database and perform some queries. You may find you do not
have enough time to complete all of this in today’s lab so please use the tutor’s time wisely and
then complete the rest in your own time.

You are expected to produce a report detailing your queries, and some reflections about how you
built the database. This report is assessed and should be handed in by 4.30pm on Monday
November 2nd for all groups.

2 Summary: What You Should Do in this Lab
• Check that you have all the tables you need in your database.
• Check that you have assigned appropriate keys to all your relations.
• Check that you have created all of the appropriate foreign key relationships.
• Populate your database with the data described on the paper tables attached to this handout.
You may choose to use the digital form of the data available on Moodle. There are instructions
for doing this later in this worksheet.
• Perform some queries on the data and then save the results.
3 Populating the Database
The sample data to populate your database can be found both on the attached sheet as tables and
as a series of text files on Moodle.
You enter the data into your database in one of two ways (you should try both).
(1) Enter the data manually row by row in the Results grid view (Section 3.1)
(2) Import the data as text files into the table using the import facility (Section 3.2).
NB – before you do either of these – you must check the following:
(i) The data in the tables has the same number and type of attributes/fields as the tables in
the text files/on the sheet
(ii) The attribute/field names are in the same order
(iii) The data types of the actual data and those that are in your tables match.
CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 2
If any of the above does not match, then you can change your database OR the order of data in the
text files. If in doubt – please see your tutor. It is best to avoid changing the data.
Finally, the order that you populate the tables is important: you should populate the entities
BEFORE populating the weak entities or relationships, to ensure that foreign key constraints can
be met – remember, the value inserted in a foreign key column MUST already exist in the
referenced primary key. So plan ahead the order that you are going to import data into the tables.
3.1 Importing Manually
The Results grid offers a way to manually enter data. To use this, right click the table you wish to
populate in the left hand side Schema explorer, then click ‘Select Rows – Limit 1000’. You can
then enter the data for a new row by clicking the last row (the one full of NULL values) – see
Figure 1. After you enter a new row, click Apply to send that row to the database.

Figure 1: Manual data entry in Result grid view in MySQL Workbench
3.2 Importing Data from Files
The data is also located in text files on the 1F pages of Moodle in the ‘IM Tutorials and Labs’
Folder. The files each have a.csv suffix – CSV stands for ‘comma separated values’, which is
the format of the text files.
(i) Download the data (text files) into your personal filespace.
(ii) Check that the data in the text files matches the column headings in your tables. You
can use the Notepad++ tools to open the files – using Excel is not recommended!
(iii) If data in text files doesn’t match your tables – just change the text files (or your
database schema if easier).
(iv) Import the text files one by one into all the tables in your DB (see Figure 2).
To import the text files into your tables – select the ‘Import Records’ button from the Results
grid toolbar (see Figure 2).

Figure 2: Importing external data (text files) into your database tables in MySQL
Workbench.
CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 3
Select the file you want to import (they should all be saved to your workspace) and press Open.
The Results grid is now updated – this see allows you to check that the data is in the form that the
table expects. You MUST now click the Apply changes to upload the data to the server. Only at
this stage will you be informed of any problems importing the data (see the next section).
3.3 Dealing with Importing Problems
We expect you to have difficulties in importing the data. Dealing with such problems is typical of
working with data in real-life databases. Problems when importing are usually caused by one of
three things:
1. data type mismatch – “ERROR 1406 (22001): Data too long for column
'xxx' at row 1”. This happens if declared data types and the type of the data in the
text files don’t match. Change the types to match by altering the table definition. The
ordering of columns being different between the table definitions and the data files might
cause this.
2. data type mismatch for NULL – “ERROR 1366: 1366: Incorrect integer
value: 'NULL' for column xxx' at row 1”. This happens if you are trying
to import a NULL value as a String, in quotes. You can edit the SQL for the insert manually
to name ‘NULL’ into NULL.
3. primary key constraints – “Error Code: 2627. Violation of PRIMARY KEY
constraint. Cannot insert duplicate key in object”. This happens,
for example your primary key includes too many or too few columns. Check that your table
definition has an appropriate primary key.
4. foreign key constraints – “Error Code: 1452. Cannot add or update a
child row: a foreign key constraint fails”. This happens, for example,
if you are importing the data for a foreign key, but haven’t yet populated the referenced
primary key yet. Think carefully about how to import values into Staff table.
NB - Before moving on to the queries - check all your data has been imported for all tables by
viewing each table in the Results grid.
The important outcome is to populate your database with as much of the data from the example
text files as possible in order for you to be able to query the database properly.
4 Querying Your Database
• Given the small amount of data in your database, it would be possible to answer the following
queries by just scanning the tables. However, the point of this exercise is to become familiar
with the techniques for querying, by practicing identifying the appropriate SQL design pattern,
and creating the correct SQL to query the database.
• As you perform your queries, save the results using the following naming scheme: Query
. For example, the first query will be saved as 'Query 1'.
4.1 An Example
You are tasked to “Find the name of the student with Student ID 1235000”.
Step 1: Consider the possible SQL Design Patterns. In this example, all of the data we need is
within a single row of the Student table, so the Basic query pattern is appropriate.

CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 4
Step 2: Identify what is needed to apply the pattern: projection(s), table(s), condition(s). We
need to project forename and lastname attributes from the Student table. The condition we need to
apply is selecting only row(s) where the matric_no column must be equal to 1235000.

Step 3: Generate the new SQL query from the
design pattern and what you identified in Step 2.

Step 4: Enter the query into MySQL Workbench.
Make a new Query Tab in MySQL Workbench (File...New Query Tab), and enter your SQL query.
Click the Execute button to run the query. If you have a syntax error (such as misspelling an
attribute name) in your SQL, examine the Action Output at the bottom of the screen, then correct
the error. Otherwise, the results will appear in the Results grid (see Figure 3 below).

Figure 3: Viewing results of a query in MySQL Workbench.
Step 4. Close the Query Tab, export and naming the SQL and results of the query. Remember
that the tutors may wish to view the result of your queries. Use the Export button on the
Results grid to save your results in CSV format. Finally, use the File...Save Script As to save the
SQL query you ran as a file with a .sql extension.
4.2 Queries to Run
Write queries, and save the results and SQL queries. For queries 1-4, also write out the relation
algebra. Remember to consider the SQL design patterns for each query.
1. Find the job title (i.e., lecturer, professor, etc.) of staff member Stephen Brewster. [1]
2. Find the exam results for all students taking CS-1Q. [1]
3. Show the results of query 2 with the students’ names, and order the results in ascending order
alphabetically by surname. [1]
4. Find the names of all staff lecturing on ‘CS-1Q’ who are also professors. [1]
5. Find the number of staff members of each job title (Hint: consider the Grouping design
pattern). [1]
6. Find the names of tutors who are not lecturers and whose tutorial group meets in room 11.
(Hint: (1) Create a query to select the tutors whose group meets in room 11 and then (2)
extend the first query using the join pattern with the Staff table.) [1]
SELECT forename, lastname
FROM Student
WHERE matric_no = 1235000
CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 5
7. For each assessed student, display their student ID and their average exam and practical
marks. [1]
8. Show the results of query 7 with the students' names, but only for those with the surname
‘Smith’ or ‘Saunders’. [1]
9. Find the names of staff who manage themselves. [2]
5 Assessment
Your assessment in the Information Management part of the CS1F course will be based on the
work you've done in designing, constructing and querying this database. There are three parts to
the assessment:

Assessment When Marks
ER Diagram handed in (week 2) 6
Queries Include in report (wk 7) 10
Report due Monday of week 7 14
(24 including
queries)
The total of 30 marks will be converted to 8% of the total practical assessment for CS1F.
5.1 Individual Report
Write a report – in Microsoft Word or PDF format:
1. Queries & Results - (10 marks)
Provide the relational algebra for queries 1-4 ONLY [don’t worry about the ordering for query
4].
Provide the SQL and results for queries 1-9. You can copy in the text of the query, and the
CSV (or take a screenshot of part of MySQL Workbench).

2. Challenges & Reflections - no more than 500 words (14 marks)
(i) Summarise the main design and building process for constructing a database. Why are ER
diagram necessary in designing the database? Why is the transition from conceptual to logical
design important? [5 marks]
(ii) Discuss the challenges you faced when constructing your database. What major problems
did you have in carrying out the building and querying of the database, and how did you
overcome them? [7 marks]
(ii) Include with a brief reflection on your personal achievements in doing this work (in other
words, what you now know that you didn't know before or what you can now do that you
couldn't do before). Consider your achievements in terms of their importance and the level of
effort you had to expend. If you still have particular difficulties (i.e. things you believe you
should have achieved, but haven't (yet)), identify them. This will be useful to you when
revising. [2 marks]
CS1F Information Management


_____________________________________________________________________________
CS1F IM 2020-2021 Practical 4 (Lab) Page 6
Note: 2 ‘modifier’ marks can be deducted for the quality of the presentation (-2 = poor
presentation; -1 = good but clarity and/or organisation could be improved; 0 = excellent
presentation)
Submission: Your report – in PDF format only – must be submitted on the Moodle submission
instance no later than 4.30pm on November 2nd FOR ALL GROUPS.

欢迎咨询51作业君