Batabase

create database galeria_d_arte
use galeria_d_arte

-- a) Crear dos tipos de datos. –
-- declaracion de tipos de datos--
exec sp_addtype t,text
exec sp_addtype n,numeric
exec sp_addtype fecha,smalldatetime

--tablas de la base de datos--
create table zips(
zip char(5),
city varchar(15)not null,
states char(2)not null,
primary key(zip))

create table artist(
artistidnumeric(6)not null identity,
firstname varchar(15)not null,
lastname varchar(20)not null,
interviewdate datetime,
interviewername varchar(35),
areacode char(3),
telephonenumber char(7),
street varchar(50),
zip char(5),
saleslastyear numeric(8,2),
salesyeartodate numeric(8,2),
socialsecuritynumber char(9),
usualmedium varchar(15),
usualstyle varchar(15),
usualtype varchar(20),unique(socialsecuritynumber),
unique(firstname,lastname),
primary key(artistid),
foreign key(zip)references zips(zip));

create table collector(
socialsecuritynumber char(9),
firstname varchar(15)not null,
lastname varchar(20)not null,
interviewdate datetime,
interviewername varchar(35),
areacode char(3),
telephonenumber char(7),
street varchar(50),
zip char(5),saleslastyear numeric(8,2),
salesyeartodate numeric(8,2),
collectionartistid numeric(6),
collectionmedium varchar(15),
collectionstyle varchar(15),
collectiontype varchar(20),
primary key(socialsecuritynumber),
foreign key(collectionartistid)references artist(artistid),
foreign key(zip)references zips(zip));

create table potencialcustomer(
potencialcustomerid numeric(6)not null identity,firstname varchar(15),
lastname varchar(20),
areacode char(3),
telephonenumber char(7),
street varchar(50),
zip char(5),
datefilledln datetime,
preferredartistid numeric(6),
preferredmedium varchar(15),
preferredstyle varchar(15),
preferredtype varchar(20),
primary key(potencialcustomerid),
foreign key(zip)references zips(zip),
foreign key(preferredartistid)referencesartist(artistid));

create table artwork(
artworkid numeric(6)not null identity,
artistid numeric(6)not null,
worktitle varchar(50)not null,
askingprice numeric(8,2),
datelisted datetime,
datereturned datetime,
dateshown datetime,
statusvar char(15),
workmedium varchar(15),
workmsize varchar(15),
workstyle varchar(15),
worktype varchar(20),
workyearcompeted char(4),collectorsocialsecuritynumber char(9),
unique(artistid, worktitle),
foreign key(artistid)references artist (artistid),
foreign key(collectorsocialsecuritynumber)references collector (socialsecuritynumber),
primary key(artworkid))

create table show
(
showtitle varchar(50),
showfeaturedartistid numeric(6),
showclosingdate datetime,
showtheme varchar(50),
showopeningdate datetime,
foreignkey(showfeaturedartistid)references artist (artistid),
primary key(showtitle)
)

create table shownln(
artworkid numeric(6)not null identity,
showtitle varchar(50),
primary key(artworkid,showtitle),
foreign key(artworkid)references artwork(artworkid),
foreign key(showtitle)references show(showtitle));

create table buyer(
buyerid numeric(6)not null identity,
firstname varchar(15)not null,lastname varchar(20)not null,
street varchar(50),
zip char(5),
areacode char(3),
telephonenumber char(7),
purchaseslastyear numeric(8,2),
purchasesyeartodate numeric(8,2),
primary key(buyerid),
foreign key(zip)references zips(zip));

create table salesperson(
socialsecuritynumber char(9),
firstname varchar(15)not null,
lastname varchar(20)not null,
street varchar(50),
zipchar(5),
unique(firstname,lastname),
primary key(socialsecuritynumber),
foreign key(zip)references zips(zip));

create table sale(
invoicenumber numeric(6),
artworkid numeric(6),
amountremittedtoowner numeric(8,2),
saledate datetime,
saleprice numeric(8,2),
saletax numeric(6,2),
buyerid numeric(6)not null,
salespersonsocialsecuritynumber char(9),
primary key(invoicenumber),...