Nile main page

Welcome to Nile Company's website. This is website and its database backend were done as a student project for Introduction to Databases (EECS 647) at the University of Kansas.

Database schema

ER diagram (2009-03-16) (13.8 kiB)

ER diagram (2009-03-16) (14.4 kiB)

Users relation

CREATE TABLE users (
   user_id INTEGER,
   user_since DATE,
   username CHAR(32),
   password CHAR(32),
   first_name CHAR(32),
   last_name CHAR(32),
   email_address CHAR(64),
   PRIMARY KEY (user_id));

Orders relation

CREATE TABLE orders (
   order_id INTEGER,
   ordered_by INTEGER,
   order_date DATE,
   order_status SMALLINT,
   PRIMARY KEY (order_id),
   FOREIGN KEY (ordered_by) REFERENCES users (user_id));

Products relation

CREATE TABLE products (
   product_id INTEGER,
   price DECIMAL(6,2),
   stock_qty INTEGER,
   weight REAL,
   PRIMARY KEY (product_id));

Books relation

CREATE TABLE books (
   product_id INTEGER,
   isbn CHAR(17),
   title CHAR(64),
   author CHAR(64),
   subject CHAR(64),
   PRIMARY KEY (product_id),
   FOREIGN KEY (product_id) REFERENCES products (product_id));

Videos relation

CREATE TABLE videos (
   product_id INTEGER,
   title CHAR(64),
   genre CHAR(64),
   rating CHAR(5),
   PRIMARY KEY (product_id),
   FOREIGN KEY (product_id) REFERENCES products (product_id));

Contains relation

CREATE TABLE contains (
   order_id INTEGER,
   product_id INTEGER,
   qty INTEGER,
   PRIMARY KEY (order_id,product_id),
   FOREIGN KEY (order_id) REFERENCES orders (order_id),
   FOREIGN KEY (product_id) REFERENCES products (product_id));

Database queries

Browse books query

SELECT b.isbn,b.title,b.author,b.subject,p.price,p.stock_qty
FROM products p,books b
WHERE p.product_id=b.product_id;

Browse videos query

SELECT v.title,v.genre,v.rating,p.price,p.stock_qty
FROM products p,videos v
WHERE p.product_id=v.product_id;