Systems Analysis and Database Design | Realcode4you

Objectives

Entity Relationship (ER) Diagram–Multiple and Self-referencing relationships

• Introduction to SQL

• Sample database (used in lectures and labs)

• Simple SELECT statements

• SQL Server & SQL Server Management Studio

Multiple Relationships

• In the examples in the previous lecture, there was only one relationship between two given entities– This is NOT a rule

• Multiple relationships may occur when two entities are linked in more than one way

• Imagine a primary school database: Year

  • Each Class (e.g. “Year 6”) has multiple students

  • Each Class has a Head Boy

  • Each Class has a Head Girl

  • Each year may have a head boy and a head girl, and it is not required for a student to be a head boy or girl

  

When multiple relationships exist, it is important to give FKs meaningful names, and consider naming the relationships

Other examples of multiple relationship scenarios:

“A flight departs from one airport and lands at another”

“Groups can have many students and must have a leader”

Self-Referencing Relationships

• A company database, with departments and employees

  – One department may have multiple employees

  – Each department also has a department manager

Relational Database Terminology

ERD to Database Tables

Each entity in a physical ERD translates directly to a table

Database Table Creation

Once a physical model of a normalised system has been created, it’s time to implement it as an actual database

• The first step is to create the tables of your database table

• Each entity in your physical model will become a

• The order of table creation is important, in order to ensure the existence of primary and foreign keys in relationships

– You cannot have a foreign key column that refers to a table that has not yet been created

Sample Database – “Company”

- job_grade has no FKs – creation order not important

- The relationship in red is added after creation and population to prevent issues with circular references

The Company Database

Order of Table Creation

 • The basic rule to remember when creating tables is:

 • Creating Unit, then Student, then Enrolment would also be appropriate

– as long as Unit and Student are created before Enrolment

Order of Table Dropping

• Deleting a table in a database is known as dropping it

• The rule to remember for this is:

• Essentially… “All tables with foreign keys be dropped before the table they reference is dropped” “The many side of a one-to-many relationship must always be dropped before the one side”

• In case it isn’t obvious… “The drop order is the reverse of the creation order”

Data Dictionaries

• Once a well-structured and normalised database has been designed via normalisation and/or ER modelling, it is almost ready to implement it as an actual database in a DBMS dictionary includes:

• The last step in a good design is to create a data

• A data dictionary should contain all the information needed to implement the database in a DBMS. This Includes:

– The names of all entities and their attributes

– The domain of all attributes (data types, constraints, etc)

– Details of all primary and foreign keys

– Written descriptions of entities, attributes, relationships, etc, where needed

(e.g. for anything confusing or ambiguous)

• Data dictionaries typically take the form of a number of tables – one table per entity

–Order the tables in an appropriate table creation order, or remember to

specify this information in the data dictionary

• A data dictionary should contain everything that someone needs to know to implement the database in a DBMS

• Some columns of the data dictionary refer to data types and constraints used in the database/DBMS itself

–We will cover this in upcoming weeks

Brief Introduction to SQL

• Structured Query Language (SQL) is the language used to send commands to a database in a RDBMS, including…

– Commands to retrieve data from a database schemas

• (Standard SQL queries using the “SELECT” command)

– Commands to insert, update or delete data in a database

• (Data Manipulation Language - DML)

– Commands to create, modify and delete database

• (Data Definition Language – DDL)

– Commands to manage users access control to a database

• (Data Control Language – DCL)

• All these languages (DML, DDL, etc) are part of SQL, and have consistent syntax style and structure– They are defined only by their purpose

• SQL is a standardised language supported by just about every RDBMS, but many “variations” exist

– While the common/basic syntax for most commands remains the same, some

commands have different syntax

– They also add features which are often only supported by certain products who

have implemented that variation

– It is unwise to rely heavily on such features, as this limits your ability to transfer

your database from one DBMS to another

Common SQL Commands

 • Here is a list of some common SQL commands…

Creating Tables in SQL

•  A table creation statement in SQL consists of several basic elements:– The words CREATE TABLE

– The name of the table

– An opening parenthesis

– Column definitions (separated by commas)

• Includes name, data type and other properties of each column

– Constraint definitions (separated by commas)

• e.g. Key fields, unique fields…

– A closing parenthesis

– A SQL terminator (;)

•  Remembering that each entity in your physical ERD maps to a table, let’s make a “student” table

–Some DBMSs do not support “#” in a field name, so we use alphanumeric

characters and underscores only

Dropping Tables in SQL

•  Since we just discussed it, let’s start with something simple: Dropping tables in a database using SQL statements

• The syntax for this statement is simply:

– DROP TABLE ;

• In regards to our last example…

DROP TABLE ApplianceOwner;

DROP TABLE Appliance;

DROP TABLE Tenant;

DROP TABLE Unit;

• Each statement ends with a ; (semicolon)

•  Here’s another example, this time with a compound key…

• Notes:

–Both the invoice and item ids are part of the primary key (we have omitted foreign

keys in this example)

–Item_id is CHAR(10) rather than an INT… Why?

–Quantity is TINYINT, which accepts anything from 0 to 255, and it has a default

value of 1 – used if no quantity specified

Foreign Keys

• The foreign key, or referential integrity constraint, designates a column, or combination of columns, as a foreign key and establishes a relationship to a primary key (or a unique key) in another table (or even the same table).

• Create order must be followed – primary key must already exist in order to create foreign key constraint with it

• Example - create tables for the simple enrolment model:

• Student table and Unit table must be created first

  
  

  

•  And then the enrolments table…

  

  
  

• enrolment_id is primary key, using IDENTITY property to implement an auto-incrementing integer

• Foreign key constraints simply specify the FK field, and the PK field that it refers to (inside the name of its home table)

Capabilities of SELECT Statement

Basic SELECT Statement: Syntax

• SELECT clause identifies which column(s)

• FROM clause identifies which table(s)

• WHERE clause identifies which row(s)

–Where clause is optional

–Only those rows whose values make the conditions true will be returned

Selecting all columns

Selecting Specific Columns

Guideline for writing SQL statements

• Goals: correct syntax, readability and easy to edit

– SQL statements are not case sensitive

– SQL statements can be on one or more lines

– Keywords cannot be abbreviated or split across lines

– Keywords are typically entered in UPPERCASE

– New clauses are usually placed on separate lines

– Indents are used to enhance readability

Arithmetic Expressions

Can create expressions with number and date data by using arithmetic operators.

– Add (+), subtract (-), multiply (*) and divide (/)

– Other operators exist, but these are the most common

Operator Precedence:

– Multiplication and division take priority over addition and subtraction

– Operators of the same priority are evaluated from left to right

– Parentheses are used to prioritise evaluation and to clarify statements

 SELECT job_id, min_salary, max_salary, max_salary*1.05 FROM job;

SELECT job_id, min_salary, max_salary, max_salary*1.05 FROM job;

Column Aliases

A column alias:

– Renames a column heading for the results of that query names may be missing,

unhelpful or ambiguous

– Is useful with calculations and other situations where column

• Usual form is AS 'aliasname' after the column name

– The AS is optional, but recommended for clarity

– If the alias contains spaces or special characters, you must enclose it in single

quotation marks

• If the alias is a single word with no special characters, the quote marks can

be omitted

SELECT job_title AS Job, max_salary AS 'Pre-raise Maximum', max_salary*1.05 AS 'Post-raise Maximum' FROM job;

Concatenation

• A function named CONCAT() allows you to join multiple text-based columns into a single column of results

SELECT CONCAT(job_id, job_title) AS 'Job ID & Title' FROM job;

 • You can add other text to this in single quotes as needed

SELECT CONCAT(job_id, ' (', job_title, ')') AS 'Job ID & Title' FROM job;

 • A common use of concatenation is to produce full names…

SELECT CONCAT(first_name, ' ', last_name) AS 'full_name' FROM employee;

SELECT first_name + ' ' + last_name AS 'full_name' FROM employee;

Duplicate Rows & the DISTINCT Keyword

 • By default, a query will display all rows, including rows which contain the same values (i.e. duplicates)

 • You can eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause.

Summary of SELECT Basics

 • That covers the basics of the SELECT statement

– Selecting all columns with *

– Specifying which columns to select by column name

– Arithmetic operators

– Column aliases

– Concatenation

– Using the DISTINCT keyword to eliminate duplicates

• These can all be combined in order to select something very specific from a table

• In coming weeks, we will learn to specify criteria with the WHERE clause, and connect columns from different tables using joins

If you have any query or questions, you can contact us for more details:

realcode4you@gmail.com