Simple Car Rental database

Question

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

Answer 1

ok. It looks like you have 4 tables here with nothing to join them together. Perhaps the easiest solution at this point would be to create a mapping table called something like CarRental which would have the columns Carpoolid, cilentsid, dateinid, and dteoutid.

I think the way I would go would be to drop the datein and dateout table and put them on a table called car rental.

    CREATE TABLE [dbo].[CarRental](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CarpoolID] [int] NOT NULL,
    [ClientID] [int] NOT NULL,
    [DateIN] [datetime] NULL,
    [DateOut] [datetime] NOT NULL,
 CONSTRAINT [PK_CarRental] PRIMARY KEY CLUSTERED 
(
    [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

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_CarPool] FOREIGN KEY([CarpoolID])
REFERENCES [dbo].[CarPool] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_CarPool]
GO

ALTER TABLE [dbo].[CarRental]  WITH CHECK ADD  CONSTRAINT [FK_CarRental_Clients] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Clients] ([Id])
GO

ALTER TABLE [dbo].[CarRental] CHECK CONSTRAINT [FK_CarRental_Clients]
GO

Car rental now stores the relationship between the client and the car that they rented and for each record stores the datein and dateout.

If you dont want to script all of this out you can use the table designer to create the table by right clicking on tables and then choosing new table. You can use the database diagram to drag and drop columns in order to create your foreign key relationships by right clicking database diagrams and chosing New Database diagram.

If you use this structure then You would leave the DateIn field as NULL untill the car was returned. Your query to find out which clients and cars have not been returned would be

SELECT carpool.brand, clients.name, clients.lastname
  from carrental 
       join carpool on carpool.id = carrental.carpoolid
       join clients on clients.id = carretnal.cientid
where carretnal.datein IS NULL

In this case you dont need to update the records for people who havent returned a car. You only update the record to contain the date when they have returned the car.

UPDATE CarRental
SET Datein = GETDATE()
WHERE Clientid = @CLientid
  AND Carpoolid = @Carpoolid