PRACTICAL 4  

Topic Review/Preview1. What is a null value?2. The relational model is based on the concept of a relation which is physically represented as a table.

A relation is represented as a two dimensional table in which the rows of the table correspond toindividual records and the columns correspond to attributes.

In the relational model, what is a tuple?What is the domain of an attribute?

3. Label the relation name, primary key and attributes of the following relation:

EmployeeDept(empId, lastName, firstName, departmentName)

4. Create an ER model for each of the following descriptions:a. Each company operates four departments, and each department belongs to one company.b. Each department in part (a) employs one or more employees, and each employee works for one

department.c. Each of the employees in part (b) may or may not have one or more dependents, and each

dependent belongs to one employee.d. Each employee in part (c) may or may not have an employment history.e. Represent all the ER models described in (a), (b), (c) and (d) as a single ER model.

Practical Questions5. Tutors please discuss the concepts of DDL, DML, CREATE TABLE, NOT NULL, as those in the

slides for lecture 5 (Introduction to SQL), especially for those prac classes that are scheduled ahead ofthe lecture time on the same week.

6. Optional: SQL Server Visual Track 1 - Table Construction

In this unit, our visual construction of databases or tables are already well illustrated in the practicalsfor the first 2 weeks, through the use of Microsoft Access. Now that we have moved on to the morepowerful Microsoft SQL Server, a commercial database server of industry strength, we will focusmore on the direct use of SQL statements or scripts to create/manage database tables and conduct dataqueries. Nevertheless, SQL Server actually offers a more powerful GUI that can be utilised to visuallycreate, modify, and query databases. However, we will not formally explore another GUI in this unit,this time for the SQL Server, and will instead leave it as an optional pursuit in the form of severalvisual tracks to those interested students.

In other words, we will in this unit follow up a sequence of Visual Tracks with Microsoft SQL Server,to see how a great deal of database management and queries can be done through the graphics userinterface, so that students will be able to optionally explore how such GUI activities can berepresented or translated into commands in SQL, and how GUI alone can fulfill most of the databaserelated activities.

In this very first part we will examine how to create tables through the GUI by Microsoft SQL Server.This gives an alternative and visual way of creating tables, an approach very similar to that wealready experienced with Microsoft Access. If you create tables and enter data there via the GUI, youcan also get it to generate SQL scripts for the table creations and queries.

a. First login via the Microsoft SQL Server Management Studio

b. In the left pane for Object Explorer, locate the entry for your own database

c. Right click on the tables you wish to delete first, then choose delete

d. To create the table Hotel in GUI, right click Tables and then choose New Tables...

so as to reach

e. Enter the 3 fields for the table Hotel as

right click the field to set the primary key as

then right click the tab for the new table or go through the File menu to save the newlycreated table as Hotel.

f. Select Refresh from the menu item View if necessary, then you will be able to see the newtable on the Object Explorer pane

g. We now enter a few records into the table Hotel. Right click table name in the ObjectExplorer pane and select Edit Top 200 Rows, then you will get

enter 3 records and we get

h. To conclude this part, we click the icon New Query at the top left to create a new windowfor SQL script. We then enter

select * from Hotel;

to display all the records within the table Hotel

We note that it is important that students retain their name in any of their submitted ordemonstrating screen shots as is shown within the red circle in the above image.

7. SQL Exercise 1

Structured Query Language (SQL) has a complete data definition language (DDL) and datamanipulation language (DML). SQL will be discussed fully during lectures, however to provide anintroduction, login to the Database Design and Development SQL Server database (as per instructionsearlier in semester) and enter in the following DDL SQL command to create the EmployeeDept tablewhich corresponds to the relation in Question 3 above.

CREATE TABLE EmployeeDept ( empId nvarchar(6), lastName nvarchar(20), firstName nvarchar(20), departmentName nvarchar(30), CONSTRAINT empId_pk PRIMARY KEY(empID));

We note that the semicolon at the end of the statement is essential as it is the 'statement terminator'. Inregard to the most used basic data types for SQL Server, the following is a quick summary. Theparameters in red square brackets are optional, and the red square brackets themselves should benon-existent.

Data Type Descriptionchar[(n)] datatype can store up to 8000 bytes of fixed-length character data; you

can specify the maximum byte length with nvarchar[(n)] datatype can store up to 8000 bytes of variable-length character data;

you can specify the maximum byte length with n; variable-lengthmeans that character data can contain less than n bytes, and the storagesize will be the actual length of the data entered

nvarchar[(n)] variable-length Unicode data with a maximum length of 4,000characters; Unicode datatypes take twice as much storage space asnon-Unicode datatypes

datetime datatype can store dates from January 1, 1753, to December 31, 9999,

with accuracy of 3.33 milliseconds; if you do not specify date portionof the datetime value, then January 1, 1900 is supplied; if you do notspecify time portion of datetime value, then 12:00:00:000AM issupplied

numeric[(p[, s])] p - is a precision, that specify the maximum total number of decimaldigits that can be stored, both to the left and to the right of the decimalpoint

s - is a scale, that specify the maximum number of decimal digits thatcan be stored to the right of the decimal point, and it must be less thanor equal to the precision.

A typical SQL command to create a table, in a simplified form, takes the form

CREATE TABLE TableName ( field1_name data_type, field2_name data_type, .... PRIMARY KEY (attrib_p1 [, attrib_p2 ...]), .... );

8. SQL Exercise 2

Start the SQL Serve Management Studio. Logon to your database, as described in the instructions inthe first practical. Select your database and ensure you can view all items.

Click on New Query button to start a new SQL Query browser window. You will not be using theTable option to create tables throughout the following exercises.

Use CREATE TABLE statement to create the Hotel, Room, and Guest relations, as described on page166 (or 158 for the 5th edition) of the textbook. After writing the SQL statements click the !Executebutton or click F5 to run the query. Errors will be highlighted in the Messages window below thequery window. We note that one would typically use varchar datatype instead of char datatype,when one expects null values or a variation in data size.

If you make a mistake, remove the table with DROP TABLE statement followed by the table name ina new query, e.g. DROP TABLE Hotel; Next, create the Booking Table.

Use separate SQL queries for each create table statement and save under appropriate names, such asCreateHotelTable.sql, etc.

Create the primary keys. Do not include foreign keys at this stage -- we will be using SQL statementsto do this later.

Additional ExercisesWe note that most questions in the Additional Exercises part will be parallel or very similar to theexamples illustrated earlier on or in the previous practicals.

α. Consider the database case of keeping record of which user is currently holding which book from alocal library. Create 2 entity types Book and User and a relationship type Borrows.

a. Suppose each library book needs to be recorded merely for the bookId and the book title,and each user needs to be recorded merely for the userId and the userName. Draw thediagrams for the entity types Book and User. What other attributes are likely to be usefulin these entity types?

b. Assume each user may borrow up to 10 books in total. Draw the full ER diagram for thissimple database and also specify the occurrence multiplicities for the relationship.

c. Similar to the steps in Introduction to MS ACCESS (or in the above Visual track 1), useMicrosoft Access (or SQL Server's graphical user interface) to design the tables Book,User and Borrows. Specify the respective primary keys, and enter 2-3 records into eachof these tables. (This is a update from the previous Book/Author/Writes tables, so that it ismore consistent with the earlier subquestions. It is still acceptable if you already did it withthose Book/Author/Writes tables.)

Hints: Compare with a similar table trio Vocalist, Performs and Song in Practical 3, andsee how their corresponding tables are generated. The tables User, Borrows and Bookare also similar to those Order, OrderDetails (Contains) and Product in Invoice in MSAccess of Practical 2. Essentially it means Borrows could be of the structure

BorrowsPrimary key attribute for User PKPrimary key attribute for BookOther pertinent attributes if any

d. (optional) Likewise use SQL Server's GUI to display the table design and the table recordsfor these 3 tables. Get the screen shots for the display (by for example pressing ALT-PrintScreen and then pasting it somewhere).

Note: Students are not required to complete the questions, or part of the questions, that aremarked "optional". However, students are also highly encouraged to complete or attemptsuch questions as their own additional exercises.

ACKNOWLEDGEMENT: This document may have included excerpts from the prescribed textbook by Thomas Connolly and Carolyn Begg, and isalso based on an earlier delivery some years back at WSU by Dr Eshan Vossough who may have in turn made use of other contributors' work.

Compiled and typeset by Zhuhan Jiang @2021.