ER diagram Example of the Procedures Univers-Esoterique.com

Univers Search Engine Database Scheme

1. Brief database scheme

Because table H_relate_Cat has only one field (H_URL) more than table Category_Info, we add H_URL field to table Category_Info and delete table H_relate_Cat. After the refinements, we make the Univers Search Engine DATABASE SCHEME as

Customer_Info ( username, password, kind_of_account, C_name, occupation, sex, C_email, C_country, C_town, C_comments)

Home_site_Info(H_URL, H_title, H_keywords, H_description, H_score, H_visit)

Category_Info( Cat_name, Subcategory,Cat_description, H_URL)

C_have_H ( username, H_URL, since)

2. Database scheme in detail

1) Customer_Info (C_username, C_password, kind_of_account, C_name, occupation, sex, C_email, C_country, C_town, C_comments)

R={A,B,C,D, E, F, G, H, I, J}, where A= username, B= password, C= kind_of_account, D= C_name, E=occupation, F=sex, G=C_email, H=C_country, I=C_town, J=C_comments

F={ A®B, A®C, A®D, A®E, A®F, A®G, A®H, A®I, A®J}

Primary key: A

F is in BCNF

Customer_Info
C_username	varchar(40)
C_password	varchar(20)
kind_of_account	varchar(20)
C_name	varchar(80)
occupation	varchar(40)
sex	int
C_email	varchar(40)
C_country	varchar(20)
C_town	varchar(40)
C_comments	varchar(500)

2) Home_site_Info(H_URL, H_title, H_keywords, H_description, H_score, H_visit)

R={A,B,C,D,E,F},

where A= H_URL, B= H_title, C= H_keywords, D= H_description

E=H_score, F= H_visit

F={A®B,A®C,A®D,A®E,A®F}

Primary key: A

F is in BCNF

Home_site_Info
H_URL	varchar(200)
H_title	varchar(200)
H_keywords	varchar(800)
H_description	varchar(500)
H_score	int
H_visit	int

3) Category_Info( Cat_name, Subcategory,Cat_description, H_URL)

R={A,B,C, D}, where A= Cat_name, B= Subcategory, C= Cat_description, D=H_URL

F={AB®C, AB®D}

Primary key: AB

F is in BCNF.

Category_Info
Cat_name	varchar(200)
Subcategory	varchar(200)
Cat_description	varchar(500)
H_URL	varchar(200)

4) C_have_H ( C_username, H_URL, since)

R = {A, B, C}, where A = username, B = H_URL, C = since

F = {AB ® C }

Primary key: AB

Foreign key: A reference Customer_Info, B reference Home_site_Info

F is in BCNF.

C_have_H
C_username	varchar(40)
H_URL	varchar(200)
Since	date