Member Avatar for mandy2612

hi i'm an absolute fresher in DBMS and our teacher gave us an assignment in this there are two questions,
we have to draw the UML diagram,
find the association,
ER diagram,and then to write SQL queries
the problem is that i'm little bit confused about the attributes i think this is the first step so can u help me in making the UML diagram
as soon as possible
i have attached my views
thanks

A_video_shop_database.doc (26 KB)
  • 4 Contributors
  • 19 Replies
  • 188 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by SkinHead
Member Avatar for ithelp

Storeloan has 1:n relation with Video Iid
Storeloan has 1:n relation with Books via Iid
Storeloan has m:n relationship with Customers
By the way no one will do you assignment unless if you put some effort on it.

Member Avatar for mandy2612

yes i knw that no one will do my assignment thats why i just asked for checking that attributes and nothing else so that i can make the UML diagram understood

Member Avatar for mandy2612

hi
yes i'm,,
and wat about u

Member Avatar for mandy2612

so
wat course r u doing and where.
wats ur name ,, and whr r u from basically

Member Avatar for patrick18

dmdd wat bout u

Member Avatar for patrick18

is this assign for u due on friday

Member Avatar for mandy2612

sr wat is dmdd
and my assignment is due on 18th may

Member Avatar for patrick18

@ ithelp can u explain how wrote M:n between customers and loan
only 1 customer wud take 1 or many loan at a time
many customer wont take 1 loan at a time.

Member Avatar for patrick18

18th may is friday

Member Avatar for mandy2612

yes it is due on friday
i'll send the relationship diagram can u plz let me know wether the relationship is correct or not

Member Avatar for SkinHead

Hey,

I've Attached The Relationship Diagram.

It Might Not Mean Very Much Without The Table Contents To Flesh It Out. But I'll Get To That Soon.

How Much Detail Do You Want To Go Into With The SQL. I'm Writing Stored Procedures & Functions To :

Manage Staff (Add, Change, Remove)
Manage Customers (Add Change, Remove)
Manage Items (As Above)
Manage Loan Transactions...etc

Hope This Helps.

VideoShop_Database_Relationships.JPG 80.28 KB
Member Avatar for mandy2612

hi Rob ,,
how r u. thanks for ER diagram. Rob i'm sending u the tables this is according to my knowledge u tell me wats wrong with this waiting for ur reply

table 1 table 5
books: Condition_codes:
Book_id Condition_code
Book_title Condition_description
Book_pg_no
Category_name table 6
Rental_dialy_rate Categories:
Condition_code Categoy_name
Category_description
table 2 Daily_overdue_rate
Video:
Video_id table 7
Video_title Rental_status_code:
Video_length Rental_status_code
Video_description Rental_status_description
category_name
Rental_daili_rate table 8
Format_Types:
table 3 Format_type_code
Customer: Format _type_description
Customer_id
Customer_first_name table 9
Customer_last_name Staff_members:
Customer_birth_date Staff_name
Customer_phn Pay_rate_id
Condition_code

table4 table 10
Customer_rentals: Salary:
Staff_name Pay_rate_id
Item_rental_id Date_work
Customer_id Start_time
Format_type_code End_time
Rental_status_code
Rental_date_out
Rental_date_returned table 11
Rental_amount_due Video_stores:
Store_id Store_id
Store_name
Store_address
Store_phn

Member Avatar for mandy2612

i'm resending the table

assignment_DBMS.doc (25 KB)
Member Avatar for SkinHead

Hey,

I've Attached The Reply.

What About The SQL ? Have You Got SQL Server Or Some Other RDMS To Play With ?

assignment_DBMS_Reply.doc (34.5 KB)
Member Avatar for mandy2612

sry but i dont knw how to send private msges on daniweb can u tell me plz

Member Avatar for SkinHead

I've Attached The SQL & Explanation For Both Queries

I Hope This Helps.

Rob

Here Is The Query For 'The Average Number Of Items Borrowed By A Customer On A Date':

SELECT		COUNT(1)	 		AS	'LoanCount',
		(COUNT (DISTINCT iCustomer_ID))	AS	'CustomerCount',
		(CAST(COUNT(1) AS Float) / (COUNT (DISTINCT iCustomer_ID))) AS	'Average',
		CAST(dtLoaned as VarChar(11))	AS	'DateLoaned'
FROM		tblLoan
Group BY	CAST(dtLoaned as VarChar(11))

To Explain It :

SELECT	COUNT ( 1 )				Will Count Up The Number Of Items Loaned
	(COUNT (DISTINCT iCustomer_ID))		will Count Up The Number Of Distinct Customers
	(CAST(COUNT(1) AS Float) / (COUNT (DISTINCT iCustomer_ID)))	This Will Give The Average. 
									It Is Important That We Include	CAST(COUNT(1) AS Float) To Make The Average A Float
	CAST(dtLoaned as VarChar(11))		will Strip Out The Time From A Date Time Field - 01/01/2006 12:00:00 Becomes 01/01/2006
FROM		tblLoan
Group BY 	CAST(dtLoaned as VarChar(11))	This Groups The Counts & Averages Into Seperate Counts/Averages Depending On The Date.

** Please Be Aware That This Is Done In SQL Server. Oracle Does Not Have A VarChar Type-Use VarChar2(11) In Oracle. 
   I Don't Think Oracle Has A specific Float Type, But Will Accept It In A SQL Query

*****

Here Is The Query For 'The Names Of The Customers Who Have Got The Maximum Number Of Overdue Items In The Past'

DECLARE	@MaxOverdueCount	Int

SELECT  @MaxOverdueCount	=	MAX(tblTemp.overduecount)
FROM	(	SELECT	C.iCustomer_ID		AS	'Customer',
			COUNT(L.iLoan_ID)	AS	'OverdueCount'
			FROM	tblCustomer	C,
				tblLoan	L	
			WHERE	C.iCustomer_ID		=	L.iCustomer_ID
			AND	L.iLoanStatus_ID	=	2
			group by C.iCustomer_ID		)	AS tblTemp

SELECT	 C.vcCustomerName
FROM	tblCustomer	C,
		(	SELECT	C.iCustomer_ID	AS	'Customer',	
			COUNT(L.iLoan_ID)	AS	'OverdueCount'
			FROM	tblCustomer	C,
				tblLoan	L	
			WHERE	C.iCustomer_ID		=	L.iCustomer_ID
			AND	L.iLoanStatus_ID	=	2
			group by C.iCustomer_ID		)	AS tblTemp
WHERE		C.iCustomer_ID		=	tblTemp.Customer
AND		tblTemp.OverdueCount	=	@MaxOverdueCount

I've Had To Make This A Two Stage Query. My Head Hurts Trying To Do It One, But I Know That Some SQL Commands Don't Work In Oracle.

The (	SELECT	C.iCustomer_ID		AS	'Customer',
		COUNT(L.iLoan_ID)	AS	'OverdueCount'
		FROM	tblCustomer	C,
			tblLoan	L	
		WHERE	C.iCustomer_ID		=	L.iCustomer_ID
		AND	L.iLoanStatus_ID	=	2
		group by C.iCustomer_ID		)	AS tblTemp

Creates A Temp Table With A List Of Customers & The Number Of Overdue Items Associated With Them. I've Had To Put An Extra Field/Attribute In The tblLoan Table. This Is iLoanStatus_ID - It Has A Value Of 2 When The Loan Is Overdue.

The tblLoan Table Links To A Seperate (New) Table tblLoanStatus With The Following Structure

pk	iLoanStatus_ID		Integer
	vcLoanStatusDesc	String

& Contents

iLoanStatus_ID	vcLoanStatusDesc
1		On Loan
2		overdue

First, Find The Maximum Number Of Overdue Items For A Single Customer. This Is Assigned To The Local Variable @MaxOverdueCount.

Then Select The Customer Name For The Entry In tblTemp That Has The Highest OverdueCount.

Est Voila !!!!

I Know It's Late, But I Hope You Manage To Do Something With This Before The Deadline.

I'll Be Up & About For A While Yet If You've Got Any Questions. Don't Worry About Being Confused. It's Often The Case That You'll Have To Get Something Horribly Wrong Before Understanding Dawns. It Happens To Me More Than I'd Care To Admit.

Cheers

Rob
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.