Practical Session 13

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

2. 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 tables • Data Manipulation Language • Used to manipulate data in databases, insert, delete, and retrieve • We will use SQL (Structured Query Language)data definition and query language. • Each database consists of a number of tablesand 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

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

4. 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 arrays • 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). • 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.

5. Creating/Deleting a table CREATE TABLE STORE( Id integer PRIMARY KEY, Name varchar(30) NOT NULL, Type varchar(30)); - SQL statements need to end with a semicolon to separate between different statements.

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

7. 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 in the database are permitted.

8. Code

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

10. SQL Commands

11. PRACTICAL_SESSIONS TEACHING_ASSISTANTS

12. Insert/Delete/Update [record]

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

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

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

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

17. WHERE • AND • OR • IS (IS NOT NULL) • IN • BETWEEN • LIKE • http://en.wikipedia.org/wiki/Where_%28SQL%29

18. HAVING/GROUP BY

19. HAVING/GROUP BY

20. ORDER BY • If not specified, default order is ascending.

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

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

23. Notes • 1:Nrelations are encoded by a single foreign key in the table that has the N end: • Movies <N :1 > Director (In general a 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 <N : N > Actors (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. • If you assume that a movie can have several directors, then you need a cross table movies_directors as well. • If you assume that additional information on directors may not be added, or that names of directors are not modified – then you may use the field directorName directly in the movies table.

24. Solution

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

26. 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);

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

28. Answer? • Inglorious Bastards • Django Unchained

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

30. Example

31. Abstract Factory Pattern • Done by creating a class which its sole purpose is creating requested objects. • 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.

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

33. 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 creationself-explanatory, consider factory pattern over regular constructors. • Constructor names must