Unidad 4 Actividad 1

(1)

LIC. SISTEMAS COMPUTACIONALES ADMINISTRATIVOS

FACULTAD: ADMINISTRACION

EXPERIENCIA EDUCATIVA: BASE DE DATOS

DOCENTE: CARLOS ARTURO TORRES GASTELU

TEMA: ACTIVIDAD 1

CAPTURAR BASE DE DATOS DE PRUEBA

INTEGRANTES:

GABRIELA HERNANDEZ PAXTIAN

KARINA BAIZABAL LAGUNES

GRUPO: C002

SEMESTRE: 5

(2)

INTRODUCCION

En este trabajo mostramos como se realizó la base de datos de prueba de la actividad 1 de la unidad 4.

Esta nueva base de datos se realizó mediante el Management Estudio de SQL Server, todo fue realizado por medio de la interfaz.

Los Script que insertamos fueron ejecutados después que creamos nuestra base de datos.

La base de datos se refiere a un banco en el cual tiene muchas funciones y relaciones que verán mas adelante.

También se muestran los registros que insertamos en nuestra base de datos, ya que con ellos podremos realizar consultas.

Para tener una visión más acerca de esta base de datos consulte las páginas siguientes.

(3)

SCRIPTS DE TABLAS ACCOUNT

CREATE TABLE [dbo].[account](

[account_id] [int] NOT NULL,

[product_cd] [varchar](10) NOT NULL,

[cust_id] [int] NOT NULL,

[open_date] [date] NULL,

[close_date] [date] NULL,

[last_activity_date] [date] NULL,

[status] [varchar](10) NULL,

[branch_id] [smallint] NULL,

[avail_balance] [decimal](10, 2) NULL,

[pending_balance] [decimal](10, 2) NULL,

[open_emp_id] [smallint] NULL,

CONSTRAINT [PK_account] PRIMARY KEY CLUSTERED

(

[account_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY

= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF GO

ALTER TABLE [dbo].[account] WITH CHECK ADD CONSTRAINT

[FK_account_branch] FOREIGN KEY([branch_id])

REFERENCES [dbo].[branch] ([branch_id])

GO

ALTER TABLE [dbo].[account] CHECK CONSTRAINT [FK_account_branch]

GO

[FK_account_customer] FOREIGN KEY([cust_id])

REFERENCES [dbo].[customer] ([cust_id])

GO

ALTER TABLE [dbo].[account] CHECK CONSTRAINT [FK_account_customer]

GO

[FK_account_employee] FOREIGN KEY([open_emp_id])

REFERENCES [dbo].[employee] ([emp_id])

GO

ALTER TABLE [dbo].[account] CHECK CONSTRAINT [FK_account_employee]

GO

[FK_account_product] FOREIGN KEY([product_cd])

REFERENCES [dbo].[product] ([product_cd])

(4)

ALTER TABLE [dbo].[account] CHECK CONSTRAINT [FK_account_product]

GO

BRANCH

CREATE TABLE [dbo].[branch](

[branch_id] [smallint] NOT NULL,

[name] [varchar](20) NULL,

[address] [varchar](30) NULL,

[city] [varchar](20) NULL,

[state] [varchar](2) NULL,

[zip] [varchar](12) NULL,

CONSTRAINT [PK_branch] PRIMARY KEY CLUSTERED

(

[branch_id] ASC

) ON [PRIMARY]

BUSINESS

CREATE TABLE [dbo].[business](

[state_id] [varchar](10) NULL,

[incorp_date] [date] NULL,

CONSTRAINT [PK_business] PRIMARY KEY CLUSTERED

(

[cust_id] ASC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[business] WITH CHECK ADD CONSTRAINT

[FK_business_customer] FOREIGN KEY([cust_id])

(5)

CUSTOMER

CREATE TABLE [dbo].[customer](

[fed_id] [varchar](12) NULL,

[cust_type_cd] [char](2) NULL,

[address] [varchar](30) NULL,

[city] [varchar](20) NULL,

[state] [varchar](20) NULL,

[postal_code] [varchar](10) NULL,

CONSTRAINT [PK_customer] PRIMARY KEY CLUSTERED

(

[cust_id] ASC

) ON [PRIMARY]

DEPARTMENT

CREATE TABLE [dbo].[department](

[dept_id] [smallint] NOT NULL,

CONSTRAINT [PK_department] PRIMARY KEY CLUSTERED

(

[dept_id] ASC

) ON [PRIMARY]

EMPLOYEE

CREATE TABLE [dbo].[employee](

[emp_id] [smallint] NOT NULL,

[fname] [varchar](20) NULL,

[lname] [varchar](20) NULL,

[start_date] [date] NULL,

[end_date] [date] NULL,

[superior_emp_id] [smallint] NULL,

[dept_id] [smallint] NULL,

[title] [varchar](20) NULL,

[assigned_branch_id] [smallint] NULL,

CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED

(

[emp_id] ASC

) ON [PRIMARY]

GO

(6)

GO

ALTER TABLE [dbo].[employee] WITH CHECK ADD CONSTRAINT

[FK_employee_branch] FOREIGN KEY([assigned_branch_id])

GO

ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [FK_employee_branch]

GO

[FK_employee_department] FOREIGN KEY([dept_id])

REFERENCES [dbo].[department] ([dept_id])

GO

ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [FK_employee_department]

GO

[FK_employee_employee] FOREIGN KEY([superior_emp_id])

REFERENCES [dbo].[employee] ([emp_id])

GO

ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [FK_employee_employee]

GO

INDIVIDUAL

CREATE TABLE [dbo].[individual](

[fname] [varchar](30) NULL,

[birth_date] [date] NULL,

CONSTRAINT [PK_individual] PRIMARY KEY CLUSTERED

(

[cust_id] ASC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[individual] WITH CHECK ADD CONSTRAINT

[FK_individual_customer] FOREIGN KEY([cust_id])

GO

ALTER TABLE [dbo].[individual] CHECK CONSTRAINT

[FK_individual_customer]

GO

OFFICER

CREATE TABLE [dbo].[officer](

[officer_id] [smallint] NOT NULL,

(7)

[title] [varchar](20) NULL,

[start_date] [date] NULL,

[end_date] [date] NULL,

CONSTRAINT [PK_officer] PRIMARY KEY CLUSTERED

(

[officer_id] ASC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[officer] WITH CHECK ADD CONSTRAINT

[FK_officer_business] FOREIGN KEY([cust_id])

REFERENCES [dbo].[business] ([cust_id])

GO

ALTER TABLE [dbo].[officer] CHECK CONSTRAINT [FK_officer_business]

GO

PRODUCT

CREATE TABLE [dbo].[product](

[product_cd] [varchar](10) NOT NULL,

[product_type_cd] [varchar](10) NULL,

[date_offered] [date] NULL,

[date_retired] [date] NULL,

CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED

(

[product_cd] ASC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[product] WITH CHECK ADD CONSTRAINT

[FK_product_product_type] FOREIGN KEY([product_type_cd])

REFERENCES [dbo].[product_type] ([product_type_cd])

GO

ALTER TABLE [dbo].[product] CHECK CONSTRAINT [FK_product_product_type]

(8)

PRODUCT TYPE

CREATE TABLE [dbo].[product_type](

[product_type_cd] [varchar](10) NOT NULL,

CONSTRAINT [PK_product_type] PRIMARY KEY CLUSTERED

(

[product_type_cd] ASC

) ON [PRIMARY]

GO

TRANSACTION

CREATE TABLE [dbo].[transaction](

[txn_id] [int] NOT NULL,

[txn_date] [datetime] NULL,

[account_id] [int] NOT NULL,

[txn_type_cd] [varchar](10) NULL,

[amount] [decimal](10, 2) NULL,

[teller_emp_id] [smallint] NOT NULL,

[execution_branch_id] [smallint] NOT NULL,

[funds_avail_date] [datetime] NULL,

CONSTRAINT [PK_transaction] PRIMARY KEY CLUSTERED

(

[txn_id] ASC

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[transaction] WITH CHECK ADD CONSTRAINT

[FK_transaction_branch] FOREIGN KEY([execution_branch_id])

GO

ALTER TABLE [dbo].[transaction] CHECK CONSTRAINT

[FK_transaction_branch]

(9)

DIAGRAMA ENTIDAD RELACION

account account_id product_cd cust_id open_date close_date last_activity_date status branch_id avail_balance pending_balance branch branch_id name address city state zip business cust_id name state_id incorp_date customer cust_id fed_id cust_type_cd address city state postal_code department dept_id name employee emp_id fname lname start_date end_date superior_emp_id dept_id title assigned_branch_id individual cust_id fname lname birth_date officer officer_id cust_id fname lname title start_date end_date product product_cd name product_type_cd date_offered date_retired product_type product_type_cd name transaction txn_id txn_date account_id txn_type_cd amount teller_emp_id execution_branch_id funds_avail_date

(10)

REGISTROS

branch

INSERT INTO branch(branch_id, name, address, city, state, zip)

VALUES ('101','centro','Independencia #143','Veracruz','Veracruz','91712'); INSERT INTO branch(branch_id, name, address, city, state, zip)

VALUES ('102','norte','Pinos #89','Veracruz','Veracruz','91713'); INSERT INTO branch(branch_id, name, address, city, state, zip) VALUES ('103','perisur','1 de mayo #54','Veracruz','Veracruz','91610'); INSERT INTO branch(branch_id, name, address, city, state, zip)

VALUES ('104','sur','francisco I. madero #24','Veracruz','Veracruz','91618'); department

INSERT INTO branch(dept_id, name) VALUES ('201','Gerencia');

INSERT INTO branch(dept_id, name) VALUES ('202','Contaduria');

INSERT INTO branch(dept_id, name) VALUES ('203','Atencion a cliente');

employee

INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('1','Michael','Smith','1/01/2010','','','201','Gerente general', '101'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('2','Susan','Sarker','1/03/2010','','1','201','Gerente', '102'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('3','Robert','Tyler','5/06/2010','5/10/2010','2','203','Cajero', '102'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('4','Susan','Hawthorne','6/12/2010','6/02/2011','2','203','Cajero', '102'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('5','John','Gooding','6/12/2010','6/02/2011','2','203','Cajero', '102'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('6','Helen','Fleming','7/01/2011','12/12/2011','2','203','Cajero', '102'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('7','Chris','Tucker','8/02/2010','','1','202','Gerente', '103'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('8','Sarah','Parker','9/04/2011','11/06/2011','7','203','Cajero', '103'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

(11)

VALUES ('10','Tomas','Ziegler','7/07/2011','12/10/2011','7','203','contador', '103'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('11','Paula','Parker','2/03/2010','9/06/2011','7','203','contador', '103'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('12','Samantha','Jameson','17/05/2011','','1','201','Gerente', '104');

VALUES ('13','John','Blake','29/05/2011','29/08/2011','12','203','Cajero', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('14','Cindy','Mason','23/07/2010','23/07/2011','12','203','Cajero', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('15','Frank','Portman','7/09/2011','20/10/2011','12','203','Cajero', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('16','Theresa','Markham','5/03/2010','5/03/2011','12','203','Cajero', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('17','Beth','Fowler','6/06/2010','28/12/2011','12','203','Cajero', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date, superior_emp_id, dept_id, title, assigned_branch_id)

VALUES ('18','Rick','Tulman','1/02/2010','1/03/2011','12','202','Contador', '104'); INSERT INTO employee(emp_id, fname, lname, start_date, end_date,

VALUES ('19','Ruben','Blades','7/08/2010','8/08/2011','12','202','Contador', '104');

product type

INSERT INTO product type(product_type_cd, name) VALUES ('301','Ahorro');

INSERT INTO product type(product_type_cd, name) VALUES ('302','retiro');

INSERT INTO product type(product_type_cd, name) VALUES ('303','deposito');

INSERT INTO product type(product_type_cd, name) VALUES ('304','credito');

(12)

product

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('501','super nomina','301','2/01/2010','3/05/2010');

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('504','Targeta de credito','304','8/07/2011','8/10/2011');

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('505','Premier','301','16/05/2011','21/08/2011');

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('506','Universitario','301','9/09/2011','21/12/2011');

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('507','Universia','301','9/04/2011','9/06/2011');

INSERT INTO product(product_cd, name, product_type_cd, date_offered, date_retired) VALUES ('508','Accionista','301','7/09/2011','12/12/2011');

customer

INSERT INTO customer(cust_id, fed_id, cust_type_cd, address, city, state, postal_code)

VALUES ('601','BZLGKR891220','FF','Fracc. Lomas del Rio M. #99','Veracruz','Veracruz', '913456');

VALUES ('602','GLMGMI900320','KK','Lopez Mateo #45','Catemaco','Veracruz', '919957');

VALUES ('603','JHLTKM871023','JH','Col. centro las vagas','Tierra blanca','Veracruz', '913905');

(13)

VALUES ('605','LZLGMB88303','LZ','Carrizal #34','Veracruz','Veracruz', '913456');

VALUES ('606','LEMGKR89403','LE','Tlaxcoco #65','Cabada','Veracruz', '910467'); account

INSERT INTO account(account_id, product_cd, cust_id, open_date, close_date, last_activity_date, status, branch_id, avail_balance, pending_balance, open_emp_id) VALUES ('701','501','601','4/04/2010','5/12/2010',

'5/06/2010','activada','102','600.00','100.00','3' );

'5/06/2010','activada','102','1000.00','200.00','3' );

'8/06/2010','activada','103','900.00','300.00','9' );

'12/10/2010','activada','104','700.00','100.00','14' );

'11/11/2010','activada','104','200.00','100.00','15' );

'1/02/2011','activada','102','400.00','200.00','6' );

transaction

INSERT INTO transaction(txn_id, txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id, funds_avail_date)

VALUES ('1','2011-02-22','701','DBT','1000.00','17','104','10-02-2011 03:34:23 p.m.'); INSERT INTO transaction(txn_id, txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id, funds_avail_date)

(14)

INSERT INTO transaction(txn_id, txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id, funds_avail_date)

VALUES ('3','01/06/2011','706','DBT','200.00','11','103','01/06/2011 09:00:30'); INSERT INTO transaction(txn_id, txn_date, account_id, txn_type_cd, amount, teller_emp_id, execution_branch_id, funds_avail_date)

VALUES ('4','23/10/2011','706','DBT','100.00','19','104','23/10/2011 10:30:40');

officer

INSERT INTO officer(officer_id, cust_id, fname, lname, title, start_date, end_date) VALUES ('801','601','Manuel','Perez','Proveedor','4-10-2011','3-12-2011');

INSERT INTO officer(officer_id, cust_id, fname, lname, title, start_date, end_date) VALUES ('802','602','Jaime','Hernandez','Proveedor','9-02-2011','10-12-2011'); INSERT INTO officer(officer_id, cust_id, fname, lname, title, start_date, end_date) VALUES ('803','603','Marcos','Garcia','Proveedor','8-11-2011','12-12-2011'); INSERT INTO officer(officer_id, cust_id, fname, lname, title, start_date, end_date) VALUES ('804','604','Angelica','Lara','vendedora','9-11-2011','3-12-2011');

business

INSERT INTO business(cust_id, name, state_id, icorp_date) VALUES ('601','Gonzalo','UYT666','4-10-2011');

INSERT INTO business(cust_id, name, state_id, incorp_date) VALUES ('602','Miriam','HFTEG6','5/05/2011');

INSERT INTO business(cust_id, name, state_id, incorp_date) VALUES ('603','Casimiro','JUYT67','6/03/2011');

INSERT INTO business(cust_id, name, state_id, incorp_date) VALUES ('604','Monica','SDRE66','17/06/2011');

individual

INSERT INTO individual(cust_id, fname, lname, birth_date) VALUES ('601','Samuel','Sanchez','5-06-1984');

INSERT INTO individual(cust_id, fname, lname, birth_date) VALUES ('602','Carlos','Soto','6-04-1980');

INSERT INTO individual(cust_id, fname, lname, birth_date) VALUES ('603','Lucina','Castro','3-12-1976');

INSERT INTO individual(cust_id, fname, lname, birth_date) VALUES ('604','Jimena','Garcia','2-05-1983');