I am trying to learn SQL development using SQL Server 2008 R2 Database Management Studio. I was trying to model a car rental company, where clients can rent cars, and I have to know which client has rented which car on which date, and also the date the car was returned. I have tried to the following:
--create the database
create database RentACar;
use RentACar;
--create the tables
create table Clients(
Id int not null constraint pk_ClientID primary key,
Name nvarchar(255),
LastName nvarchar(255)
);
create table CarPool(
Id int not null constraint pk_CarPoolID primary key,
Brand varchar(40) not null,
Price decimal not null
);
create table DateOut(
Id int not null constraint pk_DateOutID primary key,
DateOut datetime not null
);
create table DateIn(
Id int not null constraint pk_DateInID primary key,
DateIn datetime not null
);
--fill the tables with some values
insert into Clients values (1, 'Richard', 'Castle');
insert into Clients values (2, 'Jane', 'Rizzolli');
insert into Clients values (3, 'Jack', 'Bauer');
insert into CarPool values (1, 'BMW', 125.99);
insert into CarPool values (2, 'Lexus', 105.99);
insert into CarPool values (3, 'Honda', 95.99);
insert into DateOut values (1, 2012-03-10);
insert into DateOut values (2, 2012-03-11);
insert into DateOut values (3, 2012-03-12);
insert into DateIn values (1, 2012-04-10);
insert into DateIn values (2, 2012-04-11);
insert into DateIn values (3, 2012-04-12);
--now try to connect the tables somehow...
I don't know how to connect the tables with primary and foreign keys, so as to be able to for example query a list of all people who did not return the car yet, and a field that calculates how long a car has been with a client. I would also like to create a query that allows me to update the datetime for clients that have not returned their cars.
I am only familiar with the basic SQL commands like create, add, update, insert into, and i am at the end of my wits here. Nothing I try works. I always get errors no matter what i try. I am going slowly through MS Tranact-SQL documentation but I cannot seem to find what I am looking for. I know the tables should be normalized and kept to a minimum, data should not be redundant etc... I just can't seem to find the right combination. Can someone please point me in the right direction?
@BryceAtNetwork23 thanks for editing my post with code.
@Lumpy thank you for your help. I didn't have time to check if someone had replied to my question here, and last night i was thinking how to finally solve this and came up with the same conclusion. I should have two tables, one for Clients and one for the Cars, and then one table to connect them, by simply creating a column with both dates, in and out, and then the key fact being the two columns that reference the IDs of the particular Client and a particular Car using foreign keys. Also the date(s) being in the same row with these Client ID and Car ID is the connection to the Client and Car tables. Eureka! I felt so stupid, haha, it was right there in front of my nose.
Anyway, I think SQL is amazing, and I am just beginning to grasp the possibilities as I go deeper into things, and thank God for dba.stackexchange and folks like yourself, it makes learning a whole lot easier.
Thank you