tables.sql

PURPOSE OF THE PROJECT: This Application would facilitate the huge process of managing the Ad detai

create table users
(
   uid int identity(100,1) primary key,
   uname varchar(30),
   upwd varchar(8),
   urol varchar(1)
)


create table products
(
pid int primary key,
pname varchar(30)
)




create table models
(
mid int primary key,
pid int foreign key references products(pid),
mname varchar(30),
adnum int
)




create table ads
(
adid int identity(1000,1) primary key,
pid int foreign key references products(pid),
mid int foreign key references models(mid),
uid int foreign key references users(uid),
advoice varchar(30),
adduration varchar(10),
adcontent varchar(30),
adversion varchar(10),
adrelease varchar(30),
adpath varchar(30),
adstatus varchar(20),
admoddate varchar(30),
adremarks varchar(100)
)


\Audio Files\{0}.wav




pname as Product,mname as Model,uname as [Submitted By],adstatus as Status,adremarks as Remarks,adid as AdID

pname as Product,mname as Model,advoice as Voice,adduration as Duration,adcontent as Content,adversion as Version,adrelease as Release,adpath as Name,admoddate as Modified On,adstatus as Status,adremarks as Remarks,adid as AdID



create view alldetails as 
select products.pname,products.pid,models.mname,models.mid,models.adnum,users.uid,users.uname,users.urol,ads.adid,ads.advoice,ads.adduration,ads.adcontent,ads.adversion,ads.adrelease,ads.adpath,ads.adstatus,ads.admoddate,ads.adremarks
from products,users,models,ads
where models.pid = products.pid and ads.uid = users.uid and ads.mid=models.mid

1  products.pname,
2  products.pid,
3  models.mname,
4  models.mid,
5  models.adnum,
6  users.uid,
7  users.uname,
8  users.urol,
9  ads.adid,
10 ads.advoice,
11 ads.adduration,
12 ads.adcontent,
13 ads.adversion,
14 ads.adrelease,
15 ads.adpath,
16 ads.adstatus,
17 ads.admoddate,
18 ads.adremarks