The 2 nd hand student book database
Download
1 / 30

The 2 nd Hand Student Book Database - PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on

The 2 nd Hand Student Book Database. Jon Havier High Distinction Assignment, Autumn 2007. Introduction to the 2 nd hand Student Book Database.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' The 2 nd Hand Student Book Database ' - lynley


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
The 2 nd hand student book database

The 2nd Hand Student Book Database

Jon Havier

High Distinction Assignment, Autumn 2007


Introduction to the 2 nd hand student book database
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


Student exchange website
Student Exchange Website

  • www.textbookexchange.com.au


The erd for 2 nd hand student books database
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



1 many relationship
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.


1 many relationship1

Foreign key

1:Many relationship

  • One Student can buy many textbooks (ISBN)


M m relationship
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


M:M relationship

  • One Studentemail can have many ISBN and one ISBN can have many Studentemail


Sql queries

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)


Sql query 1 simple select
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.________ ;


Sql query 1 simple
SQL query 1 - Simple

  • Show ISBN & Selleremail of all textbooks registered

  • SELECT ISBN, Selleremail FROM Jontext2007textbook;

  • ISBN | Selleremail

  • --------------+--------------------------------------------

  • 9789470811481 | [email protected]

  • Etc

  • (15 rows)


Sql query 2 natural join
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


Sql query 2 natural join1
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| [email protected] | 0400000001 | Alberto

  • (1 row)


Sql query 3 cross product version
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


Sql query 3 cross product version1
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 | [email protected]| 0400000001 | Alberto

  • (1 row)


Sql query 4 group by
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


Sql query 4 group by1
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)


Sql query 5 sub query
SQL query 5 - Sub-query

  • Essentially a query within a query

  • Used to find often a single result

  • Often used for complex queries


Sql query 5 sub query1
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)


Sql query 6 self join
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


Sql query 6 self join1
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

  • --------------+-----------------------------------

  • [email protected]| Aust. Accounting | Picker, L & Radford | 9789470811481 | $100

  • (1 rows)


Check constraints example
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


Check constraints example1
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

  • );


Sql syntax for actions
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


Sql syntax for actions1
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

  • );


Creating a view
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


An example of creating a view
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;


An example of querying a view
An Example of Querying a View

  • Query exactly as if a table

    SELECT * FROM Expensivebook;


The end
The End

  • Any Questions?


ad