I've Build A Database Schema-Open For Suggestions

Apr 18, 2007

Hello Guys:I have included the schema that i build for sql serve 2005 below, My concern is with creating views and cursors.I open for any suggestions. Please try out the scheme;

/*
Created4/5/2007
Modified4/14/2007
Project
Model
Company
Author
Version
DatabaseMS SQL 2005
*/

use master
if exists (select name from sys.databases where name='clubDataBase')
drop database clubdataBase
go
create database clubDataBase
go
use clubDatabase

Create table [Role]
(
[Role_Id] Char(3) NOT NULL,
[Role_Type] Char(30) NOT NULL,
[Description] Char(30) NOT NULL,
Primary Key ([Role_Id])
)
go

Create table [Employee]
(
[employee_id] Char(20) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[Role_Id] Char(3) NOT NULL,
[Manager_Id] Char(20) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[HireDate] Datetime NOT NULL,
[PayRate] Real NOT NULL,
[Status] Varchar(30) NOT NULL,
[SSN] Char(12) NOT NULL,
[Fname] Char(30) NOT NULL,
[Lname] Char(30) NOT NULL,
[Gender] Char(1) NOT NULL,
[DOB] Datetime NOT NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([employee_id])
)
go

Create table [Address]
(
[Add_Id] Char(10) NOT NULL,
[AddressLine1] Varchar(30) NOT NULL,
[AddressLine2] Varchar(30) NULL,
[City] Varchar(20) NOT NULL,
[State] Char(2) NOT NULL,
[ZipCode] Char(10) NOT NULL,
[Region] Char(20) NOT NULL,
Primary Key ([Add_Id])
)
go

Create table [Club]
(
[Club_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionId] Char(5) NOT NULL,
[ClubName] Varchar(30) NOT NULL,
[PhoneNumber] Char(13) NOT NULL,
[FaxNumber] Char(13) NULL,
[Email] Varchar(30) NOT NULL,
[WebSite] Varchar(30) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Club_Id])
)
go

Create table [Equip_Inven]
(
[Equip_Inven_Id] Char(5) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Equip_Id] Char(5) NOT NULL,
[Purchase_Date] Datetime NOT NULL,
[Purchase_Cost] Money NULL,
Primary Key ([Equip_Inven_Id])
)
go

Create table [Equipment]
(
[Equip_Id] Char(5) NOT NULL, UNIQUE ([Equip_Id]),
[Equip_Name] Varchar(30) NOT NULL,
[Equip_Type] Varchar(20) NOT NULL,
[Equip_Desc] Varchar(30) NULL,
Primary Key ([Equip_Id])
)
go

Create table [CustomerClass]
(
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[StartDate] Datetime NOT NULL,
[Member_Id] Char(10) NOT NULL,
[MemClassFees] Money NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Class_Id],[StartDate],[Member_Id])
)
go

Create table [Classes]
(
[Class_Id] Char(5) NOT NULL,
[ClassName] Varchar(30) NOT NULL,
[ClassDescription] Varchar(30) NOT NULL,
Primary Key ([Class_Id])
)
go

Create table [Member]
(
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[TotalFees] Money NOT NULL,
[MemberFname] Char(20) NOT NULL,
[MemberLname] Char(20) NOT NULL,
[Phonenumber] Char(12) NOT NULL,
[DOB] Datetime NOT NULL,
[Gender] Char(1) NOT NULL,
[Email] Char(20) NULL,
[LastUpdated] Datetime NOT NULL,
Primary Key ([Member_Id])
)
go

Create table [Health_Info]
(
[Member_Id] Char(10) NOT NULL,
[Doctor_Name] Varchar(20) NOT NULL,
[Allergy] Varchar(30) NULL,
[Diseases] Varchar(30) NULL,
[Emergency_Contact1] Char(20) NOT NULL,
[Emergency_Phone1] Char(12) NOT NULL,
[Contact_Relation1] Char(20) NOT NULL,
[Emergency_Contact2] Char(20) NULL,
[Emergency_Phone2] Char(12) NULL,
[Contact_Relation2] Char(20) NULL,
Primary Key ([Member_Id])
)
go

Create table [ClubClasses]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Class_Id] Char(5) NOT NULL,
[employee_id] Char(20) NOT NULL,
[ClassAgeRange] Char(10) NULL,
[ClubClassCost] Money NOT NULL,
[ClubClassSchedule] Varchar(30) NOT NULL,
Primary Key ([StartDate],[Club_Id],[Class_Id])
)
go

Create table [Plans]
(
[Plan_Id] Char(5) NOT NULL,
[SuggestedPrice] Money NOT NULL,
[PlanName] Varchar(30) NOT NULL,
[PlanDuration] Varchar(20) NOT NULL,
[Description] Varchar(30) NOT NULL,
Primary Key ([Plan_Id])
)
go

Create table [ClubPlans]
(
[Club_Id] Char(5) NOT NULL,
[Plan_Id] Char(5) NOT NULL,
[Discount] Numeric(2,2) NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Plan_Id])
)
go

Create table [MemberClubUsage]
(
[StartDate] Datetime NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[EndDate] Datetime NOT NULL,
Primary Key ([StartDate],[Club_Id],[Member_Id])
)
go

Create table [Facility]
(
[Facility_Id] Char(5) NOT NULL, UNIQUE ([Facility_Id]),
[FacilityName] Varchar(30) NOT NULL,
[FacilityType] Varchar(30) NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Facility_Id])
)
go

Create table [FacilityUsage]
(
[StartDate] Char(1) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[EndDate] Datetime NULL,
Primary Key ([StartDate],[Member_Id])
)
go

Create table [ClubFacilites]
(
[Club_Id] Char(5) NOT NULL,
[Facility_Id] Char(5) NOT NULL,
[OpenDate] Datetime NOT NULL,
[CloseDate] Datetime NOT NULL,
[Description] Varchar(30) NULL,
Primary Key ([Club_Id],[Facility_Id])
)
go

Create table [Equip_Maintanence]
(
[Equip_Maintanence_Id] Char(5) NOT NULL,
[Equip_Inven_Id] Char(5) NOT NULL,
[Main_Date] Datetime NULL,
[Main_Cost] Money NULL,
[Comment] Varchar(30) NULL,
Primary Key ([Equip_Maintanence_Id])
)
go

Create table [Guest]
(
[GuestId] Char(5) NOT NULL,
[Member_Id] Char(10) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[GuestName] Char(20) NOT NULL,
[GuestDOB] Datetime NOT NULL,
Primary Key ([GuestId])
)
go

Create table [RegionalOffice]
(
[RegionId] Char(5) NOT NULL,
[Add_Id] Char(10) NOT NULL,
[RegionName] Nvarchar(30) NOT NULL,
Primary Key ([RegionId])
)
go


Alter table [Employee] add foreign key([Role_Id]) references [Role] ([Role_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Manager_Id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([employee_id]) references [Employee] ([employee_id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [RegionalOffice] add foreign key([Add_Id]) references [Address] ([Add_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Employee] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Club_Id]) references [Club] ([Club_Id]) on update no action on delete no action
go
Alter table [Equip_Maintanence] add foreign key([Equip_Inven_Id]) references [Equip_Inven] ([Equip_Inven_Id]) on update no action on delete no action
go
Alter table [Equip_Inven] add foreign key([Equip_Id]) references [Equipment] ([Equip_Id]) on update no action on delete no action
go
Alter table [ClubClasses] add foreign key([Class_Id]) references [Classes] ([Class_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [MemberClubUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Health_Info] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [Guest] add foreign key([Member_Id]) references [Member] ([Member_Id]) on update no action on delete no action
go
Alter table [CustomerClass] add foreign key([StartDate],[Club_Id],[Class_Id]) references [ClubClasses] ([StartDate],[Club_Id],[Class_Id]) on update no action on delete no action
go
Alter table [ClubPlans] add foreign key([Plan_Id]) references [Plans] ([Plan_Id]) on update no action on delete no action
go
Alter table [Member] add foreign key([Club_Id],[Plan_Id]) references [ClubPlans] ([Club_Id],[Plan_Id]) on update no action on delete no action
go
Alter table [ClubFacilites] add foreign key([Facility_Id]) references [Facility] ([Facility_Id]) on update no action on delete no action
go
Alter table [FacilityUsage] add foreign key([Club_Id],[Facility_Id]) references [ClubFacilites] ([Club_Id],[Facility_Id]) on update no action on delete no action
go
Alter table [Club] add foreign key([RegionId]) references [RegionalOffice] ([RegionId]) on update no action on delete no action
go

--create procedures and views
--the procedure to randomly add members visit the club
create procedure getMemberClubUsage
as
declare @memID char(10),
@clubID char(10)
declare @begDate datetime
declare mem_club_cursor cursor
--randamly to add the member to visit clubs
for
select member_ID, c.club_ID
from member, club c
open mem_club_cursor
fetch mem_club_cursor into
@memID, @clubID

while @@fetch_status=0
begin
set @begDate=dateadd(day, rand()*-365, getdate())
insert into memberClubUsage
select @begDate, @clubID, @memID, dateadd(minute, rand()*300, @begDate)
fetch mem_club_cursor into
@memID, @clubID
end

close mem_club_cursor
deallocate mem_club_cursor

go
--the procedure randomly add the member to classes
create procedure getMemberClubClass
as
insert into customerClass (startDate, club_ID, class_ID, Member_ID, memclassFees)
select startDate, c.club_ID, class_ID, Member_ID, 150
from member m, clubClasses c
go

create view MembershipClubRegion_VW
as
select member_ID, memberFName, memberLName, C.Club_ID, C.ClubName, c.RegionID, r.RegionName
from member m join club c on m.club_ID=c.Club_ID
join regionaloffice r on c.regionID=r.regionID
go

create view VistorbyMonthClub_VW
as
select clubName,
datename(month, startdate)+'-'+datename(year, startdate) 'Month',
count(*) TotalVistors
from club c join memberclubusage mc on c.club_ID=mc.club_ID
group by clubName, datename(month, startdate)+'-'+datename(year, startdate)

go

create view classClub_View
as
select c.class_ID,
className,
clubName,
startDate
from classes c join clubClasses cc on c.class_ID=cc.class_ID
join club cl on cc.club_ID=cl.club_ID
go
--add testing data into database
--insert data to table Address
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0001', '100 Washington St', 'Salem', 'NH', '01123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0002', '180 Porland Ave', 'Providence', 'RI', '03123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0003', '36 Huntington Ave', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0004', '45 Marine St', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0005', '400 Tel St', 'Vermont', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('C0006', '100 Abc Ave', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0001', '180 Matthew Ave', 'Worchest', 'NH', '01145', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('B0002', '1000 Elm St', 'Braintree', 'MA', '02184', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0001', '24 Edwin St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0002', '57 Sterling Street', 'Malden', 'MA', '02148', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0003', '26 Royal St', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0004', '439 Newport Ave', 'Quincy', 'MA', '02171', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0005', '41 Old Brattleboro', 'August', 'ME', '31245', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0006', '110 N Central Ave', 'Montpelier', 'VT', '11234', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0007', '67 Marjoril Rd', 'George Town', 'CT', '1111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0008', '1 Holden Row #2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0009', '67 Marjoril Rd', 'Braintree', 'MA', '02421', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0010', '253 Cambridge St Apt#2', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0011', '30 Hill St', 'Randolph', 'MA', '02134', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0012', '87 Ocean St', 'Boston', 'MA', '02111', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0013', '7A Park Terrace', 'Boston', 'MA', '02112', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0014', '8 FoxRun #12', 'Boston', 'MA', '02113', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0015', '45 Upland Road', 'Boston', 'MA', '02114', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0016', '378 Medford St', 'Boston', 'MA', '02115', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0017', '46 Wheatland St', 'Boston', 'MA', '02116', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0018', '177 North Street', 'Boston', 'MA', '02117', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0019', '280 Breadway #4', 'Boston', 'MA', '02118', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0020', '106 Chester St', 'Boston', 'MA', '02119', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0021', '45 Upland Rd', 'Boston', 'MA', '02120', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0022', '47 Malvern St', 'Boston', 'MA', '02121', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0023', '49 Dwight St #1', 'Boston', 'MA', '02122', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0024', '97 LAWN STREET', 'Boston', 'MA', '02123', 'NE')
insert into Address ( Add_ID, Addressline1, City, State, Zipcode, Region) values ('A0025', '418 Revere Beach Pkwy #74', 'Boston', 'MA', '02124', 'NE')
go
--insert data to table RegionalOffice
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0001', 'C0001', 'New Hampshire')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0002', 'C0002', 'Rhode Island')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0003', 'C0003', 'Massachussetts')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0004', 'C0004', 'Marine')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0005', 'C0005', 'Vermont')
insert into RegionalOffice ( RegionID, Add_ID, RegionName) values ('R0006', 'C0006', 'Connecticut')
go
--insert data to table Club
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0001', 'C0001', 'R0001', 'NH CLub1', '8952645456', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0002', 'B0001', 'R0001', 'NH CLub2', '8953566889', 'mail@ClubNH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0003', 'C0003', 'R0003', 'MA Club1', '6174561787', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0004', 'B0002', 'R0003', 'MA Club2', '6175688989', 'mail@MANH', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0005', 'C0002', 'R0002', 'RI Club1', '4264567898', 'mail@ClubRI', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0006', 'C0004', 'R0004', 'Marine Club 1', '8915678989', 'mail@clubME', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0007', 'C0005', 'R0005', 'Vermont Club1', '7325641234', 'mail@clubVT', 'http://www.Club1NH.org', 'club')
insert into Club ( Club_ID, Add_ID, RegionID, ClubName, PhoneNumber, Email, website, Description) values ('C0008', 'C0006', 'R0006', 'Connecticut Club1', '7888989999', 'mail@clubCT', 'http://www.Club1NH.org', 'club')
go
--insert data to table Plans
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0001', 156.38, 'Family Plan', '1 year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0002', 90, 'Single Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0003', 70, 'Senior Plan', '1 Year', 'plan')
insert into Plans ( Plan_id, SuggestedPrice, PlanName, PlanDuration, Description) values ('P0004', 30, 'promote plan', '1 year', 'plan')
go
--insert data to table ClubPlans
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0001')
insert into ClubPlans ( Club_id, Plan_id) values ('C0001', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0002', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0004', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0005', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0006', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0007', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0008', 'P0002')
insert into ClubPlans ( Club_id, Plan_id) values ('C0003', 'P0004')
go
--insert data to table Classes
insert into Classes ( Class_id, className, classDescription) values ('A0001', 'Beginning Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0002', 'Advance Yoga', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0003', 'Swimming', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0004', 'Dancing', 'desc')
insert into Classes ( Class_id, className, classDescription) values ('A0005', 'Introduction to Tennis', 'desc')
go
--insert data to table Role
insert into Role ( Role_id, Role_Type, Description) values ('R01', 'Teacher', 'Class teacher')
insert into Role ( Role_id, Role_Type, Description) values ('R02', 'Manager', 'club manager')
go
--insert data to table Employee
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0001', 'A0001', 'R01', 'E0001', 'C0001', '1/1/1999', 15, ' ', '123456789', 'John', 'Joyce', 'M', '1/3/1968', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0002', 'A0002', 'R02', 'E0001', 'C0002', '12/1/2004', 25, ' ', '123456789', 'MacDonld', 'Harris', 'M', '10/12/1975', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0003', 'A0003', 'R02', 'E0001', 'C0003', '3/1/2006', 20, ' ', '123456789', 'Joane', 'Li', 'F', '11/5/1960', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0004', 'A0004', 'R02', 'E0001', 'C0004', '5/1/2006', 22, ' ', '123456789', 'Tony', 'Green', 'M', '8/12/1970', '4/18/2007')
insert into Employee ( Employee_id, add_id, role_id, Manager_id, club_id, HireDate, Payrate, status, SSN, Fname, Lname, Gender, DOB, LastUpdated) values ('E0005', 'A0005', 'R02', 'E0001', 'C0005', '1/1/2007', 20, ' ', '123456789', 'Lisa', 'White', 'F', '11/10/1975', '4/18/2007')
go
--insert data to table ClubClasses
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('1/1/2007', 'C0001', 'A0001', 'E0002', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('2/1/2007', 'C0002', 'A0002', 'E0003', 1500, 'Mon, Fri 7:30PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('3/1/2007', 'C0003', 'A0003', 'E0004', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/1/2007', 'C0004', 'A0004', 'E0005', 1500, 'Tue, Th 8:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('5/1/2007', 'C0005', 'A0005', 'E0002', 1500, 'Wed, Fri 7:00PM')
insert into ClubClasses ( startdate, club_id, class_id, employee_id, ClubclassCost, clubclassSchedule) values ('4/5/2007', 'C0006', 'A0004', 'E0004', 1500, 'Sat, 8:00AM')
go
--insert data to table Member
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0001', 'C0001', 'P0001', 'A0006', 153.38, 'John', 'He', '7812671567', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0002', 'C0002', 'P0001', 'A0007', 153.38, 'Joane', 'Good', '5678945689', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0003', 'C0003', 'P0001', 'A0008', 153.38, 'Sharon', 'Spears', '8123456789', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0004', 'C0004', 'P0001', 'A0009', 153.38, 'Sandy', 'Green', '9874567898', '2/18/1965', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0005', 'C0005', 'P0001', 'A0010', 153.38, 'Lisa', 'White', '7185642356', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0006', 'C0006', 'P0001', 'A0011', 153.38, 'William', 'Black', '5689741235', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0007', 'C0007', 'P0001', 'A0012', 153.38, 'Ollie', 'Green', '7812671567', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0008', 'C0008', 'P0001', 'A0013', 153.38, 'Louis', 'Brien', '5678945689', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0009', 'C0001', 'P0002', 'A0014', 90, 'Barbara', 'Stone', '8123456789', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0010', 'C0002', 'P0002', 'A0015', 90, 'Jenat', 'Jean', '9874567898', '1/17/1985', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0011', 'C0003', 'P0002', 'A0016', 90, 'Orlande', 'Moore', '7185642356', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0012', 'C0004', 'P0002', 'A0017', 90, 'Jerry', 'Dee', '5689741235', '1/19/1967', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0013', 'C0005', 'P0002', 'A0018', 90, 'Jim', 'Smith', '7812671567', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0014', 'C0006', 'P0002', 'A0019', 90, 'Jeff', 'Smith', '5678945689', '1/21/1975', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0015', 'C0007', 'P0002', 'A0020', 90, 'Michael', 'Sam', '8123456789', '1/15/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0016', 'C0008', 'P0002', 'A0021', 90, 'Michelle', 'Lee', '9874567898', '1/16/1975', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0017', 'C0003', 'P0004', 'A0022', 30, 'Judy', 'Andy', '7185642356', '1/17/1985', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0018', 'C0001', 'P0001', 'A0023', 153.38, 'Pierre', 'Pierre', '5689741235', '2/18/1965', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0019', 'C0002', 'P0001', 'A0024', 153.38, 'Tony', 'Kelliher', '7812671567', '1/19/1967', 'F', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0020', 'C0003', 'P0001', 'A0025', 153.38, 'Antonia', 'Sheeran', '5678945689', '1/20/1973', 'M', '4/18/2007')
insert into Member ( Member_ID, Club_ID, Plan_ID, Add_ID, TotalFees, MemberFName, MemberLName, PhoneNumber, DOB, Gender, LastUpdated) values ('M0021', 'C0004', 'P0001', 'A0007', 153.38, 'Scott', 'Brown', '8123456789', '1/21/1975', 'M', '4/18/2007')
go

exec getMemberClubUsage
exec getMemberClubClass
Set quoted_identifier on
go


Set quoted_identifier off
go

View 5 Replies


ADVERTISEMENT

How Build Web Application (asp.net + C# ) With Dynamic Database Schema?

Mar 26, 2008

Hi all,

I am doing an e-commerce project. This website will have a category of product. Each category will have sub-category. And sub-category may have another level of sub-category. This means the number of sub-category is not fixed. In the sub-category we will have products. Each product will have unpredicable number of properties (1, 2, 3 or many properties).

With the current requirment, I can know exactly the number of sub-category level and the number of properties. But the problem comes when later my boss add more category, sub-category ,... sub-category(more sub-category level), and product, as well as the products properties. At that time my database schema will not suitable for new category, products because the in can only design database with fixed number of sub-category level and fixed number of product properties(attributes or fiels in database).

Therefore, I want to ask all of you that
- Is there anyway to solve this problem?
- how to design database that meet extended requirements as I present above?

Thanks for all of your advices.

Quan.

View 17 Replies View Related

Schema Suggestions

Nov 20, 2007

I am working with an application currently that stores the majority of its data in the following schema.

factid1 int,
factid2 int,
factid3 int,
factid4 int,
Value decimal 14,4

To extract data from this table we are using a 4 table join to each of the factid's

Our where clause in this query is based on (where factid1 = something)

So we have a composite clustered index led by factid1.

Our plan is to reduce the size of this table by introducing the kind of schema, we would like to introduce this to keep the table size to a minimum and hopefully increase the performance of our extracts from this table.

factid4 int
intersectid int
value decimal 14,4

And then the intersect table with fact2,fact2,fact3 combinations

factid1 int
factid2 int
factid3 int
intersectid

This kind of schema reduces the size of this table substantially but performance of our extract is very poor.

Does anyone have any suggestions on schemas that will give us high performance?

Or does anyone think that the original schema will outperform any alternative schema.


View 4 Replies View Related

The 'System.Web.Security.SqlMembershipProvider' Requires A Database Schema Compatible With Schema Version '1'.

Sep 27, 2007

Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'.  However, the
current database schema is not compatible with this version.  You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!

View 4 Replies View Related

Database Schema Compatible With Schema Version '1'

Apr 12, 2008

Hello everybody!I'm using ASP.NET  3.5,  MSSQL 2005I  bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'.  However, the current database schema is not compatible with this version.  You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?

View 2 Replies View Related

Cannot Open User Default Database. Login Failed. (only When IDE Is Open)

Sep 7, 2007

I get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express

View 3 Replies View Related

Suggestions On Database Refresh

Nov 19, 2007

I have been tasked with designing an automated process to restore production data to our testing environments on an as needed basis. The schedule would revolve around our software testing and deployment schedules. I'm looking for suggestions on best practices for this task in the form of advise / links to references / etc.. Instead of presenting all of my requirements here, I'll spare you that information :). Since part of it also needs to encompass data stored in Oracle (10g). I've done a several Google searches but would like to validate / invalidate my research against the advise of the experts here.

View 17 Replies View Related

Code Suggestions For Database Searches

Apr 11, 2007

I have a database containing several tables with many different fields.  I need to create an admin section that lets me search on one field or the combination of several.  Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits. 
Thank you

View 4 Replies View Related

Need Suggestions On Text File Parsing Into Database

Feb 28, 2007

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table.  Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order.   And, they may upload columns that if not mapped, will be ignored.  Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload.   But, with the varying format of the uploaded text file, I am not sure how I could do that.  Any suggestions? Thanks! 

View 1 Replies View Related

Suggestions For A Small Database Layout...Very Simple

Jul 20, 2005

Hi ng,I need some input/suggestions for a very small layout.The situation: Some groupings of thumbnails. For every picture (thumbnail)there is a "big" picture. Thats it basically :) On the front the scenario isthis:A user clicks "Autumn". The user is presented with the "Autumn" thumbnails.If he click a thumbnail, the corresponding big image is displayed. Well, uget the picture :)Hoe is this most efficiently implemented in table-layout? I mean....do Icreate a table called "images" and have a column called "is_thumb"? Or do Ibetter make 2 tables...Or better make a "relation/type" table too?--?Thanks,/Summa

View 4 Replies View Related

Error On Build Solution, If SSIS Project Is Part Of Build

Mar 22, 2006

Hello,

we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..

C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild

Through build process we get a error:


External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):

We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?

with best regards

Anton Kalcik

View 5 Replies View Related

How Do You Build An Enterprise Database Solution?

Feb 1, 2008

Hi, I am noob, so excuse me if this is a stupid question, but I just bought a Learn HTML in 3 Hours book and a learn SQL in 10 minutes book and I was wondering how I would start to go about to build a super flexible enterprise wide web based database application, that is so flexible the users can put in any old crap in the front end and make up new fields as they go. Another one of my requirements is that it perform super fast and be distributed securely across my international multi billion dollar a year company.

So like I know Excel and a little VBA and I know how to use the query builder in Access and I landed this big old contract to revamp this old comapny's infrastructure. Should be pretty easy, right? Everyone is doing it.

So can you guys like help me out for free and stuff?

View 4 Replies View Related

Fastest Way To Build A Database Out Of SQL Scripts

Jul 23, 2005

What is the fastest way to generate an SQL 2000 databaseout of SQL scripts.The SQL scripts contain the create tables, views, storedprocedures, triggers, constraints, and the tables DATArecords.What are my options? isql? osql? are there other ways?Thank you

View 6 Replies View Related

Build Database Application On Internet With ISP?

Jul 20, 2005

I have been building ASP, ASP.NET and Crystal Reports on SQL serverfor a few years now.I have build them mostly on an Intranet or with companies that havetheir own web servers.I now need to build an application for a company that doesn't not havea web server. Just a small shop that wants an internet application.How do I build an database application when the site will be host byan ISP? The database will be SQL Server 2000, but located locally.The site will be maintained by the ISP? How does the an applicationhosted by an ISP interact with a database located locally (client'ssite)?If someone could point me toward some tutorial or tell me how to dothis, I would appreciate it.

View 1 Replies View Related

DB Design :: Bat File To Build A Database?

Dec 1, 2015

I have a SQL script folder on my HDD. The folder contains SQL Scripts and subfolders with Constraints and Extended properties. I need a BAT file that builds a database on my SQL Server instance.

I have the following structure:

 Tables (folder)
  dbo.Customers.sql
  dbo.OrderLines.sql
  dbo.Orders.sql
  dbo.Products.sql
  Constraints (folder)
           Foreign Keys(folder)
dbo.OrderLines.FK.sql
dbo.Orders.FK.sql
  Extended Properties(folder)
     dbo.Customers.EXTPRP.sql
     dbo.Products.EXTPRP.sql

The Server name for the SQL CMD is PCMSSQL2014? user: sa pass is empty.

View 3 Replies View Related

I Need To Generate The Query To Build A Database I've Designed. Please Help..

Apr 3, 2007

I've registered with GoDaddy for my site. I've used Visual Web Dev 2005 with SQL Server Mgmt Express 2005 to create my database etc. Now with GoDaddy, I need to "recreate" the database on their server again, but I don't want to go throug the whole process again. I just want to use their query editor to generate the database online.GoDaddy mentions a "personal-sql" file that SQL Server 2000 generates, but I can't find anything like that with what I have. Thanks.

View 1 Replies View Related

Want To Run Stored Procedures To Build Maintenance Database

May 13, 2015

create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.

sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt

I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.

View 1 Replies View Related

How To Build Database To Support User-specified Attributes?

Jul 23, 2005

I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information abouttheir players. Basically, allows the clubs to define custom attributes forplayers (e.g. "height", "weight", "favourite sweet").A few constraints:1. Any attributes defined is "private" to the defining club. Other clubsaren't aware of it although they may define custom attributes of their ownwith the same name and type. [Perhaps there is a way to share definitions ofidentical attributes?]2. A club doesn't have to define any custom attributes.Has anyone done anything similar?. Any ideas on how it might be done?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================

View 3 Replies View Related

How To Build Database To Support User-specified Entities And Attributes?

Jul 23, 2005

I have a database that tracks players for children's sports clubs. I haveincluded representative DDL for this database at the end of this post.A single instance of this database supports multiple clubs. I would like toadd support for letting each club define and store custom information aboutarbitrary entities. Basically, allows the clubs to define custom entities(i.e tables) and associated custom attributes (i.e. fields) that may berelated to existing tables (such as Player and FootballClub) or existingentities. For instance, a club may define a PlayerAssessment entity thatrecords all player assessments.To do this, I plan to support the following use case:1. FootballClub admin creates a new entity and gives it a name anddescription (Entity is only accessible to this FootballClub).2. FootballClub admin indicates that the new entity has a M:1 relationshipwith the Player table (this will add Player_ID as a FK attribute).- {An entity may have no relationships.}- {Relationships are also supported to other entities.}3. FootballClub admin specifies the names and domain/types of any dataattributes (i.e. fields) of the entity.- {An attribute's type may be constrained to a few allowable types likeRelationship, Integer, Float, Currency, Date, Time, DateTime, Name,Description and Memo.}4. System creates entity as specified.A few constraints:1. Any entity defined is "private" to the defining club. Other clubs aren'taware of it although they may define custom entities of their ownwith the same name and attributes. [Perhaps there is a way to sharedefinitions of identical entities?]2. A club doesn't have to define any custom entities.Ideas I've considered:1. Generate DLL and create actual tables- Restrict such customizations such that while admin is setting up entities,no other user is allowed to use the system.- Once entity definition is complete, generate an actual table using DLL.Table and column names might be changed to enforce uniqueness/validityconstraints - this suggests a need for table/column name mapping.- PROS: Easy to implement.- CONS: Doesn't scale since only a limited number of tables can be created.DDL on a live, shared system?. Scary!!All users for all clubs will be locked out while entity iscreated.2. Generate DDL and create actual tables in secondary database(s)- Same as above except that the user tables are created in secondary [,shared] databases.- PROS: Reassurance that DDL is never run on the "core" dataAll users don't have to be locked out.- CONS: Doesn't scale since only a limited number of tables can be created.{ Unless I start creating additional databases too!. }Still needs to DDL on a live, shared system.Has anyone done anything similar?. Any ideas on how it might be done?. Inparticular, is this possible without having to execute DDL on the livedatabase?Kunle=================== BEGIN DDL ===================CREATE TABLE FootballClub (Club_ID int IDENTITY,Name char(80) NOT NULL,Area char(4) NOT NULL,League char(4) NOT NULL,City char(30) NOT NULL,PRIMARY KEY (Club_ID))goexec sp_primarykey FootballClub,Club_IDgoCREATE TABLE Player (Player_ID int IDENTITY,First_Name char(30) NOT NULL,Initials char(30) NULL,Last_Name char(30) NOT NULL,Date_Of_Birth datetime NOT NULL,Position char(4) NULL,Club_ID int NULL,PRIMARY KEY (Player_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey Player,Player_IDgoCREATE TABLE UserAccount (User_ID int IDENTITY,Club_ID int NOT NULL,FullName char(80) NOT NULL,Logon char(20) NOT NULL,PWD_Hash char(60) NOT NULL,PRIMARY KEY (User_ID, Club_ID),FOREIGN KEY (Club_ID)REFERENCES FootballClub)goexec sp_primarykey UserAccount,User_ID,Club_IDgoexec sp_foreignkey Player, FootballClub,Club_IDgoexec sp_foreignkey UserAccount, FootballClub,Club_IDgo=================== END DDL ===================

View 3 Replies View Related

SQL Server 2005 Express: The Database Principal Owns A Schema In The Database, And Can Not Be Dropped.

Jan 11, 2006

I recently added a new user to my database.  Now I want to delete that user, but I keep getting the error above.  What do I need to do to delete my recently added user?

View 4 Replies View Related

SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'

Mar 21, 2007


The error message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

The log file reads:

---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.

***Background***

General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors


I'll add this from the report logs...

w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.

The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.


I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)

View 3 Replies View Related

Transact SQL :: How To Build Dynamic WHERE Clause In Openquery To Linked Database

Jul 17, 2015

I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.

SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011''  in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)

View 9 Replies View Related

How To Export Whole Database Schema To Another Database But Not Include The Data?

Sep 21, 2007


I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data.
Does anyone know how to do that?
Many thanks for replying.

View 7 Replies View Related

The Database Principal Owns A Schema In The Database, And Cannot Be Dropped.

Feb 15, 2006

Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?

The below is the TSQL that generates the my issue.



Use [master]
go
create database [test]
go

USE [test]
GO

CREATE ROLE [ReadOnlyRole]
GO

USE [test]
GO

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO

drop role [ReadOnlyRole]
go

View 12 Replies View Related

How To Build A Package To Ftp Files From BLOBs With FTP Connection Info Also Held In The Database

Apr 24, 2008

I have a table with embedded files in it and also ftp server/user/pwd fields so that each record has all the data and information required and need to configure an SSIS package to to FTP them to the destination as specified in the record within the databaseAt the moment I have a data flow task which uses a Derived Column and an Export Column transformation which creates the files to a temp folder then it runs a ForEach loop container back on the control flow to FTP each file and to then move each file to an archive folder.

All I think I need to know how to do is to somehow control the FTP connection settings with the fields from the database within this foreach loop, but it is a for each file collection. I also need to update each record saying it has been FTP'd as well
Do I need to have an initial query outside of this foreach loop to populate an array which can be used for the 4 FTP connection fields (server/user/pwd/directory), if so how?

Any ideas as to how to correctly finish this package.


Thanks

View 2 Replies View Related

The Microsoft Jet Database Engine Cannot Open MS-Access Database

Aug 18, 2007


I have MS-Access as data source for one of the reports. I can preview the report fine from BI studio however, it does not work when I deploy it on report server.

The error is :

An error has occurred during report processing.
Cannot create a connection to data source '<data source name>'.

The Microsoft Jet database engine cannot open the file '<UNC location of the MS-Access database>'. It is already opened exclusively by another user, or you need permission to view its data.




MS-Access database is located on a different server.

Any help to solve this? I understand it has something to do with permission both on server where reporting service is running as well as the server where MS-access database is located. Pls help.

View 2 Replies View Related

Database Schema

Apr 28, 2005

What would be the best way to get the Schema from a complete database.

Thanks.

View 1 Replies View Related

How To Get All Database Schema?

Sep 9, 2005

hello,

here is my problem.
I have to rebuild database after crash. there is no backup. So
I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...

thanks lot for any help.

View 1 Replies View Related

Help For Database Schema

Mar 16, 2007

Hello All,

My project uses MS SQL server database and is not too big database (have aound 200 table).

Now I have to create Database schema for my database as my project needs to be integrated with some other product.

I don't know much about database schema and how to start with it.

Can someone please give me some inputs on following:

1) What exactly database schema should include?

2) How should I start creating the schema for my database?

3) Are there any tools for doing this?



Thanks in advance

View 12 Replies View Related

Sql Database Schema

Oct 24, 2005

Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???

View 14 Replies View Related

Database Vs Schema

May 24, 2007

We currently have a product in which each client has their own Database. We adjust the connection when a user for that client logs into the system. This system has continued to grow and a good pace, but we have come to a point where failover is taking too long.



Refactoring the Database to handle multiple sites in a single database is not an option because of the time it would take to make the change. So, we are looking for another way in which this could be handle. One idea is to take multiple clients and place them in a single database using a schema to seperate them. (ex. Client A = server1.db1.schema1, Client B = server1.db1.schema2, etc).



Is there another option that would be better, or what kind of performance can we expect if we follow this path? Or, is there a way to decrease the failover time (it appears the problem is the startup of the database on the failover server)?



Thad

View 4 Replies View Related

RS Database Schema?

Oct 26, 2007



We have a RS 2000 server with a over a hundred reports, and about half as many weekly and monthly subscriptions.
In reply to my request to upgrade to RS2005, the boss asked me today for a compete list of the reports, who subscribes to them, and their delivery frequency.

He was not interested in paying for a VB or C# development effort with the SOAP API that it would require to obtain a simple list from a SQL server database, since he already has SQL programmers on staff.

So how can I get this list? Anyone know of any demo code out there?

Forgive my sarcasm and Thanks in advance.

Herb

View 4 Replies View Related

Changes To Database Schema

Oct 23, 2007



Does anyone know which database table/view one can query to get a list of all objects that have been changed by users accessing a database?

Is there any? or is there some other way of doing this?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved