Transcript Week 13

Practical Session 13
Factory Method Pattern
Abstract Factory Pattern
Structured Data Bases
Structured Query Language
Exam Questions
Factory Method Pattern
• How it is done?
– Making Constructors private/protected
– Implementing a function which its sole purpose is
creating desired objects and returning them
• create()
• open()
• Can be done by using static creation
functions.
Example
Abstract Factory Pattern
• Done by creating a class which its sole
purpose is creating objects are requested.
• The rest of the classes are not public, not part
of the interface.
• Any creation of objects need to be done by
making an instance of the Factory object and
using its methods.
Example
• http://en.wikipedia.org/wiki/Abstract_factory
_pattern#Java
When and why to use Factory over
Constructors
•
Allows developers of the framework to change the constructors when needed,
without worrying about backward compatibility.
– Frameworks which uses Constructors cannot change any constructor at all once the product is
released.
– Releasing new versions of the product requires them to keep these constructors to allow
applications which already use their framework to continue working.
– Solution? Factory Pattern.
•
Allows creation of objects when it is unknown which type to create.
– When deciding on object type relies on the state of the framework, using “new” is not
possible.
– Users of framework cannot know internal state of the framework itself.
– Using new operator already decides which object type to create.
•
When the user does not really care about many of the constructor parameters.
– Framework: Hiring Agency [agency]
– User: Company wishes to hire a Java developer, with 3 years experience.
•
•
Factory method: agency.hireDeveloper(“Java”, 3);
Constructor: new Worker(name, age, experience, skills, address, id);
– The “user” only cares about 2 things, however creating the object requires many more items.
•
If having a named method is the only way to make the creation self-explanatory,
consider factory pattern over regular constructors.
– Constructor names must
Relational Databases
• A relational database is a collection of data items organized
as a set of formally described tables from which data can
be accessed easily
• A relational database is created using the relational model:
– Data Definition Language
• Used to build and destroy databases, create, and drop
– Data Manipulation Language
• Used to manipulate data in databases, insert, delete, and retrieve
• We will use SQL data definition and query language.
• Each database consists of a number of tables and each
table has its own primary key.
• Relational:
– Because you may have relations between the different tables.
Very good tutorial at: http://www.w3schools.com/sql/default.asp
Table
ID
Name
OfficeHours
1
Achiya
Tue 10:00-12:00
2
Boaz
Sun 16:00-18:00
3
Jumana
Sun 16:00-18:00
5
Majeed
Sun 16:00-18:00
4
Ramzi
Thu 12:00-14:00
6
Yehonatan
Tue 12:00-14:00
-
Table name: TEACHING_ASSISTANTS
Column name: Id, Name, Office Hours
Column type: INT, VARCHAR(20), VARCHAR(20)
Each table has primary key, must be unique and non-null: example: id
Each line in table is called a record
You may have foreign key columns, which is a primary key in other table, to denote
relationship between two different tables
ANSI SQL Data Types
•
Character strings
–
–
–
–
•
Bit strings
–
–
•
BIT(n) — an array of n bits
BIT VARYING(n) — an array of up to n bits
Numbers
–
–
–
•
CHARACTER(n) or CHAR(n) — fixed-width n-character string, padded with spaces as needed
CHARACTER VARYING(n) or VARCHAR(n) — variable-width string with a maximum size of n characters
NATIONAL CHARACTER(n) or NCHAR(n) — fixed width string supporting an international character set
NATIONAL CHARACTER VARYING(n) or NVARCHAR(n) — variable-width NCHAR string
INTEGER and SMALLINT
FLOAT, REAL and DOUBLE PRECISION
NUMERIC(precision, scale) or DECIMAL(precision, scale)
Date and time
–
–
–
–
–
DATE — for date values (e.g., 2011-05-03)
TIME — for time values (e.g., 15:51:36). The granularity of the time value is usually a tick (100
nanoseconds).
TIME WITH TIME ZONE or TIMETZ — the same as TIME, but including details about the time zone in
question.
TIMESTAMP — This is a DATE and a TIME put together in one variable (e.g., 2011-05-03 15:51:36).
TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ — the same as TIMESTAMP, but including details about the
time zone in question.
Creating/Deleting a table
Primary Key
• A primary key is used to uniquely identify each
row in a table.
• A primary key can consist of one or more
fields on a table.
• When multiple fields are used as a primary
key, they are called a composite key.
• Primary key is inheritably unique.
Foreign Key
• A foreign key is a field(s) that point to the
primary key of another table.
• The purpose of the foreign key is to ensure
referential integrity of the data.
– Only values that are supposed to appear in the
database are permitted.
Code
Example
TEACHING_ASSISTANTS
Column
type
characteristic
ID
int
Primary Key
Name
VARCHAR(50)
Office hours
VARCHAR(9)
PRACTICAL_SESSIONS
Column
type
characteristic
TA
int
Foreign Key
Group
int
Primary Key
Location
VARCHAR(50)
Time
VARCHAR(9)
Effect: PRACTICAL_SESSIONS table cannot contain information on a TA that is not in the
Teaching Assistant table.
SQL Commands
PRACTICAL_SESSIONS
TEACHING_ASSISTANTS
TA Group
Location Time
3
11
90-234
Sun 14-16
ID
Name
OfficeHours
3
12
34-205
Sun 18-20
1
Achiya
Tue 10:00-12:00
4
13
90-125
Thu 14-16
2
Boaz
Sun 16:00-18:00
6
21
28-145
Thu 14-16
3
Jumana
Sun 16:00-18:00
1
22
28-107
Thu 14-16
5
Majeed
Sun 16:00-18:00
2
23
72-213
Thu 14-16
4
Ramzi
Thu 12:00-14:00
5
31
90-145
Thu 14-16
6
Yehonatan Tue 12:00-14:00
6
32
90-127
Thu 14-16
2
33
90-134
Thu 14-16
1
41
90-235
Thu 14-16
5
42
90-235
Thu 14-16
Insert/Delete/Update [record]
Select
• The most common operation in SQL is the query,
which is performed with the declarative SELECT
statement.
• SELECT retrieves data from one or more tables, or
expressions.
• Standard SELECT statements have no persistent
effects on the database.
• Some non-standard implementations of SELECT
can have persistent effects, such as the SELECT
INTO syntax that exists in some databases.
Select
•
•
•
A query includes a list of columns to be included in the final result immediately
following the SELECT keyword.
An asterisk ("*") can be used to specify that the query should return all columns of
the queried tables.
SELECT is the most complex statement in SQL, with optional keywords and clauses
that include:
– FROM:
•
The FROM clause which indicates the table(s) from which data is to be retrieved.
– WHERE:
•
•
The WHERE clause includes a comparison predicate, which restricts the rows returned by the query.
The WHERE clause eliminates all rows from the result set for which the comparison predicate does not
evaluate to True.
– GROUP BY:
•
•
The GROUP BY clause is used to project rows having common values into a smaller set of rows.
GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows
from a result set. The WHERE clause is applied before the GROUP BY clause.
– HAVING:
•
•
The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause.
Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the
HAVING clause predicate.
– ORDER BY:
•
•
The ORDER BY clause identifies which columns are used to sort the resulting data, and in which
direction they should be sorted (options are: ASC ascending or DESC descending).
Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.
SQL Aggregation Functions
• SQL aggregate functions return a single value,
calculated from values in a column.
– AVG() - Returns the average value
– COUNT() - Returns the number of rows
– FIRST() - Returns the first value
– LAST() - Returns the last value
– MAX() - Returns the largest value
– MIN() - Returns the smallest value
– SUM() - Returns the sum
FROM
• * returns all columns.
• You may specifically choose columns you want, and their order
WHERE
•
•
•
•
•
•
•
AND
OR
IS
IN
BETWEEN
LIKE
http://en.wikipedia.org/wiki/Where_%28SQL%29
HAVING/GROUP BY
HAVING/GROUP BY
ORDER BY
• If Unspecific, default order is undefined.
JOIN
• The JOIN keyword is used in an SQL statement to query
data from two or more tables, based on a relationship
between certain columns in these tables.
• Tables in a database are often related to each other with
keys.
• Different SQL JOINs:
– INNER JOIN/JOIN: Return rows when there is at least one match
in both tables.
– LEFT JOIN: Return all rows from the left table, even if there are
no matches in the right table.
– RIGHT JOIN: Return all rows from the right table, even if there
are no matches in the left table.
– OUTER JOIN/FULL JOIN: Return rows when there is a match in
one of the tables.
Examples: http://www.w3schools.com/sql/sql_join.asp
Exam Questions
• Write a model that holds the following
information:
– Movie: Name, publish year, origin country,
director name
– Director: Name, list of movies
– Actor: Name, list of roles in movies
Relations
• 1:N relations are encoded by a single foreign key in the
table that has the N end:
– Movies: Director is N:1:
• We wish that one movie has only one director
• N:N relations are encoded by a cross-table; two foreign
keys to the related tables with additional information
that characterizes the relation if needed
– Movies: Actors is N:N
• In general, actors play in many movies, and movies have many
actors
– In our case, the role played by the actor in the movie is
data that belongs to the cross table.
Solution
Query on table
• Write an SQL query that returns:
– Movie name, Director name, Actor name, Actor role
Multiple Roles for Actor in Movie
• We wish to add support for multiple roles for
each actor in a movie.
• Current implementation defines the primary
key of actors_movies as (actorId, movieId).
• This means that we cannot have the same
actor in the same movie more than once.
• Solution?
– Add the field role to the primary key.
– Primary key (actorId, movieId, role);
Query
• Write an SQL query that returns the roles of
“Christoph Waltz” in movies that where
directed by “Quentin Tarantino”
Answer?
• Inglorious Bastards
• Django Unchained