The 2 nd Hand Student Book Database

1. The 2nd Hand Student Book Database Jon Havier High Distinction Assignment, Autumn 2007

2. Introduction to the 2nd hand Student Book Database • 2nd Hand Student book database simulates a DMS currently in use by Student Exchange, an online website that exchanges contact details for students to sell their books. • www.textbookexchange.com.au • 2nd Hand student book is a simplified version • It aims to create a nexus between sellers and buyers advertised by both parties through certain criteria’s such as book title, edition and location. • This is a website familiar with many university students and helps in the purchase of course textbooks which can be very expensive

3. Student Exchange Website • www.textbookexchange.com.au

4. The ERD for 2nd Hand student books Database • 2nd Hand student Books – 4 tables • Sale table is a weak entity as its primary key is borrowed from Student and textbook table

5. Entities and Relationships

6. 1:Many relationship • 1:Many relationship (1:m) occurs when one record in a table is related to multiple records in another table. An example would be one student can sell many textbooks while one textbook can only belong to one student. • Therefore the seller table has a 1:m relationship with the Textbook table.

7. Foreign key 1:Many relationship • One Student can buy many textbooks (ISBN)

8. M:M relationship • M:M relationship occurs when one record in a table is related to multiple records in another table and vice-versa. An example would be one student email with many ISBN no’s and one ISBN with many student e-mail's. • Therefore a student table has a many to many (indirect) relationship with the Sale table

9. M:M relationship • One Studentemail can have many ISBN and one ISBN can have many Studentemail

10. SQL Queries SQL query 1: Simple Query of a single table SQL query 2: Natural Join SQL query 3: Cross product SQL query 4: Group By SQL query 5: Sub-query SQL query 6: Cross product (Self join)

11. SQL query 1 – Simple SELECT • A Select statement allows the database user to select and view a number of columns in a table or many tables with certain specific criteria. The output of the query depends on the selected columns, the tables required and the criteria applied to them. • SELECT ______ FROM____________ WHERE_______ HAVING __________ GROUP BY____________ Etc.________ ;

12. SQL query 1 - Simple • Show ISBN & Selleremail of all textbooks registered • SELECT ISBN, Selleremail FROM Jontext2007textbook; • ISBN | Selleremail • --------------+-------------------------------------------- • 9789470811481 | 10000001@student.uts.edu.au • Etc • (15 rows)

13. SQL query 2 – Natural Join • A natural join combines two or more tables via a common column. • The primary key of one table matches the foreign key of another table

14. SQL query 2 – Natural Join • Display ISBN, selleremail, sellerphone & sellername of all sellers currently selling the textbook with ISBN: 9789470811481 • Select ISBN, selleremail, sellerphone, sellername from Jontext2007textbook natural join Jontext2007sellerwhere ISBN = 9789470811481 ; • ISBN | Selleremail |Sellerphone | Sellername --------------+-----------------------------+-------------+--------- • 97894708114| 10000001@student.uts.edu.au | 0400000001 | Alberto • (1 row)

15. SQL query 3 – Cross Product Version • Essential a natural join but database users define the join specifically. Used for more complex joins • Using dot format, a database user will specifically detail the columns which will be joined

16. SQL query 3 – Cross Product Version • Display ISBN, selleremail, sellerphone & sellername of all sellers currently selling the textbook with ISBN: 9789470811481 in Cross-product form • Select ISBN, selleremail, sellerphone, sellername from Jontext2007textbook T1, Jontext2007seller T2Where T1.Selleremail = T2.Selleremail AND ISBN = 9789470811481 ; • ISBN | Selleremail |Sellerphone | Sellername -------------------+----------------------------+-------------+------- • 9789470811481 | 10000001@student.uts.edu.au| 0400000001 | Alberto • (1 row)

17. SQL query 4 – Group By • A group by in a table is a function that allows certain data in a table to be collapsed into one row based on a column with the same variable • Having: Similar to WHERE • Uses aggregate functions in a query, having acts like a ‘where’ in determining the condition used

18. SQL query 4 – Group By • Show the student name which have more than one book for sale and how many books they have for offer • Select studentname, count (*) as Number_of_books from Jontext2007student natural join Jontext2007Forsale group by StudentName having count (*) > 1; • Studentname | Number_of_books • --------------+------------------------ • Alberto | 3 • Bob | 3 • Cameron | 4 • David | 3 • (4 rows)

19. SQL query 5 - Sub-query • Essentially a query within a query • Used to find often a single result • Often used for complex queries

20. SQL query 5 - Sub-query List ISBN, price of all textbooks where price is the highest SELECT ISBN, Price FROM Jontext2007textbook WHERE price >= ALL (Select price FROM Jontext2007textbook); • ISBN | Price • --------------+-------------------------- • 9789470811481 | 100 • (1 rows)

21. SQL query 6 – Self-join • A self join is another version of cross join where a table is joined to its self kin order to find different rows in the table with matching elements • Often for more complex queries

22. SQL query 6 – Self-join • Show the books (ISBN) which are available from the same seller SELECT T1.Selleremail, T1.texttitle, T1.Author, T2.ISBN, T2.price FROM Jontext2007textbook T1, Jontext2007textbook T2 WHERE T1.selleremail = T2.Selleremail AND t1.ISBN < T2.ISBN; • Selleremail | Texttitle | Author | ISBN | Price • --------------+----------------------------------- • 100000001@stud| Aust. Accounting | Picker, L & Radford | 9789470811481 | $100 • (1 rows)

23. CHECK Constraints Example • Check statements prevent database user from entering data which could corrupt and create inconsistency with the database or data making it not logical for its field • They include entering names into date fields which should be a numerical field • Avoids the possibility of Murphy’s Law • ‘Things will go wrong in any given situation, if you give them a chance’ – wiki • CHECK statements don’t give them that chance

24. CHECK Constraints Example • CREATE TABLE Jontext2007textbook • ( • ISBN INTEGER NOT NULL, • Selleremail VARCHAR (50) NOT NULL, • Texttitle VARCHAR(100) NOT NULL, • Author VARCHAR(300) NOT NULL, • Edition VARCHAR(50) NOT NULL, • University VARCHAR(50) NOT NULL, • Subject VARCHAR(50) NULL, • Price INTEGER NOT NULL, • CONSTRAINT PKtextbook PRIMARY KEY (ISBN), • CONSTRAINT Price CHECK (Price > 0), • CONSTRAINT University CHECK (University = 'UTS' OR University = 'UNSW' OR University = 'MACQ' OR University = 'UWS'), • CONSTRAINT FKtextbook FOREIGN KEY (Selleremail) • REFERENCES Jontext2007Seller • ON DELETE CASCADE • );

25. SQL Syntax for Actions • Action statements are measures used to prevent the corruption of data when there is a change or a deletion of one of the records in one table. • These actions correct the relating data in the other associated tables by using a ‘cascade’ effect • Again avoids the possibility of Murphy’s Law • ‘Things will go wrong in any given situation, if you give them a chance’ – wiki • Action statements don’t give them that chance

26. SQL Syntax for Actions • CREATE TABLE Jontext2007textbook • ( • ISBN INTEGER NOT NULL, • Selleremail VARCHAR (50) NOT NULL, • Texttitle VARCHAR(100) NOT NULL, • Author VARCHAR(300) NOT NULL, • Edition VARCHAR(50) NOT NULL, • University VARCHAR(50) NOT NULL, • Subject VARCHAR(50) NULL, • Price INTEGER NOT NULL, • CONSTRAINT PKtextbook PRIMARY KEY (ISBN), • CONSTRAINT Price CHECK (Price > 0), • CONSTRAINT University CHECK (University = 'UTS' OR University = 'UNSW' OR University = 'MACQ' OR University = 'UWS'), • CONSTRAINT FKtextbook FOREIGN KEY (Selleremail) • REFERENCES Jontext2007Seller • ON DELETE CASCADE • );

27. Creating a View • Views allow database users to view a result of a statement that is used frequently without having to re-write that statement every time that view is required. • This creates ease of usability, one of the benefits of a DMS system

28. An Example of Creating a View CREATE VIEW Expensivebook (Texttitle, Price, Sellername) AS SELECT Title, Expensiveprice, Expensiveseller FROM Jontext2007textbook, Jontext2007seller WHERE textbook.Selleremail = Seller.Sellername;

29. An Example of Querying a View • Query exactly as if a table SELECT * FROM Expensivebook;

30. The End • Any Questions?