CMPT 354

Database Systems

Simon Fraser University

Spring 2013

Instructor: Oliver Schulte

Assignment 1: Entity-Relationship Modeling. The Relational Model. MS SQL Server.

Instructions: Check the instructions in the syllabus. The university policy on academic dishonesty and plagiarism (cheating) will be taken very seriously in this course. Everything submitted should be your own writing or coding. You must not let other students copy your work. Discussions of the assignment is okay, for example to understand the concepts involved. If you work in a group, put down the name of all members of your group. On your assignment, put down your name, the number of the assignment and the number of the course. Spelling and grammar count.

For the due date please see our course management server https://courses.cs.sfu.ca .

Submission and Installation Instructions are posted in a separate file on the course website.


Part I. ER Modelling

Draw a single ER diagram that represents the specifications listed below. To help you do this, we break down the drawing for you into separate steps.

1. Consider a model of a banking enterprise with customers and accounts. Customers have the following attributes: id, name, street, city. The id is unique for each customer. Accounts have the following attributes: account number, balance. There is only one account with a given account number. For each account, there is an overdraft limit amount.

Draw an ER diagram to represent these entities and their attributes.

2. Customers own accounts. For each account that a customer owns, we want to store a start date, at which the account was opened, and a pin number that gives the customer access to the account.

Extend the previous ER diagram to represent these facts, using the entities you defined in part 1.

3. Let us add three more entities to our banking model. A loan has the following attributes: loan number, loan type, and amount. Each loan has a unique loan number. The attributes of a loan payment are date, amount and number. For a given loan, the number identifies a unique payment, but payments for different loans may share the same number. For example, payment #1 for Jack Smith identifies a unique payment in the amount of $100, but payment #1 for Jackie Chan may be a different payment in the amount of $1,000. A branch has the attributes city, name, and street. Each branch has a unique name.

Extend the previous ER diagram to represent to represent these entities and their attributes.

4. Each loan is taken at a single unique branch. Customers borrow loans; a customer may have more than one loan, and a given loan may be associated with more than one customer (e.g., a couple may be co-signers on a mortgage). Using the entities you have modelled in parts 1 and 3, add relationships to represent these facts. Combine these relationships with the ones you modelled in part 2 into a single ER diagram that captures all the facts listed so far.

Note on ER exercises: ER modelling is as much a science as an art, and not a rote procedure. The purpose of this assignment is to give you a bit of practice in capturing informal descriptions in an ER diagram. Just like in real life, if you feel that not all the details that you need to know have been completely specified, use your common sense. If in doubt, explain explicitly what assumptions you are making and how they are shaping your model. Another good strategy is to follow standard examples, like those in the books and lectures, unless you have a reason for doing something different.

Total Marks for Part I: 145 Marks.

Marking Criteria:

Technical Correctness (including match between specifications and design) 60

Notational Correctness 40

Clarity of Diagram 30

Presentation (including quality of explanations, if any) 15


Part II: Translate ER into relational models.

Write SQL statements that translate your ER model for part I into the relational model (that is, the SQL statements should create tables corresponding to the entities and relationships in the ER model). Chapter 3 gives you several examples of this process. You may want to first translate the ER model into a relational schema, then the relational schema into SQL commands. E.g. in Chapter 3.1. the Students table is defined by

Students(sid: string, name: string, login: string, age: integer, gpa: real).

You are not required to write down the relational schema in this format. We won’t grade you on the schema, only on the SQL commands. We just think the schema will help you.

Be sure to incorporate as many constraints mentioned in part I as possible. If there is a constraint mentioned in part I that you cannot capture in SQL statements (using the constructs of Ch.3, that is, no ASSERT or CHECK commands), describe what the constraint is and explain why it cannot be captured.

Total Marks for Part II: 105 Marks

Marking Criteria:

Technical Correctness (including match between specifications and design) 60

Notational Correctness 30

Presentation (including quality of explanations, if any) 15


Part III: Creating Relational Schemas in SQL

1. A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee (exactly one); a child must be identified uniquely by name when the parent (who is an employee) is known. We are not interested in information about a child once the parent leaves the company.

a. Create a Relational Schema. Create one table for each of the relations required to represent this information. You may want to first draw an ER diagram to clarify the requirements for yourself. But we don’t require you to hand in an ER diagram, just the SQL commands for creating the tables. Note that this scenario is similar to the one considered in the textbook and the lectures.

b. Impose Integrity Constraints. Impose the constraints implied or stated in the description above, such as domains (e.g., integer for dno), key constraints, foreign key constraints, and participation constraints. You may use the Database Diagram feature to specify foreign key constraints in a graphical way. In addition, impose the following constraint:

Every department has exactly one manager.

Marking Criteria, Part II.

Total Marks: 87

Technical Correctness (85 marks)

Presentation, Style (15 marks)


Part IV: Programming.

7. This part requires installing SQL Server. Please see the installation instructions. Using CSIL SQL Server, it seems to be necessary to cite the database's name in the queries below. For example, for part a), the query should be
"SELECT COUNT(*) FROM AdventureWorksLT.SalesLT.Address" instead of "SELECT COUNT(*) FROM SalesLT.Address".

Execute the following commands and write down the results. We will cover the details and meanings of the commands later on in the course. For the moment, I want to verify that everyone successfully set up the AdventureWorks database, so do not worry about understanding how the commands work. [1 point each]

a) Tell me the number of records in the Address table.

SELECT COUNT(*) FROM SalesLT.Address

b) Tell me the average order quantity sold.

SELECT AVG(OrderQty) FROM SalesLT.SalesOrderDetail

c) Find the Product Models. Tell me how many there are (i.e., how many tuples/records are returned as a result of this query?)

SELECT DISTINCT(Name) FROM SalesLT.ProductModel

d) Tell me the number of addresses in Bellevue.

SELECT COUNT(*) FROM SalesLT.Address WHERE City = 'Bellevue'

e) Tell me the amount of the largest Sales Order.

SELECT MAX(TotalDue) FROM SalesLT.SalesOrderHeader

`

Total Marks: 10

Marking Criteria

Each query answer 1 point.

Setting up the system 5 points.