Practical Session 13

1. Practical Session 13 Factory Method Pattern Abstract Factory Pattern Structured Data Bases Structured Query Language Exam Questions

2. 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.

3. Example

4. 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.

5. Example • http://en.wikipedia.org/wiki/Abstract_factory_pattern#Java

6. 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

7. 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

8. Table • 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

9. ANSI SQL Data Types • Character strings • 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 • Bit strings • BIT(n) — an array of n bits • BIT VARYING(n) — an array of up to n bits • Numbers • 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.

10. Creating/Deleting a table

11. 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.

12. 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.

13. Code

14. Example TEACHING_ASSISTANTS PRACTICAL_SESSIONS Effect:PRACTICAL_SESSIONS table cannot contain information on a TA that is not in the Teaching Assistant table.

15. SQL Commands

16. PRACTICAL_SESSIONS TEACHING_ASSISTANTS

17. Insert/Delete/Update [record]

18. 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.

19. 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 SQLaggregationfunctions 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 orDESC descending). • Without an ORDER BY clause, the order of rows returned by an SQL query is undefined.

20. 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

21. FROM • * returns all columns. • You may specifically choose columns you want, and their order

22. WHERE • AND • OR • IS • IN • BETWEEN • LIKE • http://en.wikipedia.org/wiki/Where_%28SQL%29

23. HAVING/GROUP BY

24. HAVING/GROUP BY

25. ORDER BY • If Unspecific, default order is undefined.

26. 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 atleast one match in bothtables. • 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

27. 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

28. Relations • 1:Nrelations 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:Nrelations 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.

29. Solution

30. Query on table • Write an SQL query that returns: • Movie name, Director name, Actor name, Actor role

31. 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_moviesas (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);

32. Query • Write an SQL query that returns the roles of “Christoph Waltz” in movies that where directed by “Quentin Tarantino”

33. Answer? • Inglorious Bastards • Django Unchained