|
Hi all,
i need to create a movie database catalogue .CATALOGUE of movies, actors and directors.
Since i have the basic knowledge of SQL , can anyone help me in this case ? It would be better if u guyz help me here ....How can i add photos to this database ??
thnx
|
|
|
|
|
flemmard wrote: i need to create a movie database catalogue .CATALOGUE of movies, actors and directors.
Since i have the basic knowledge of SQL , can anyone help me in this case ?
Homework assignment? Think about what you want to store and then work out how to store it. For example: Actors are people. Directors are people. An actor can also be a director. Should you have a people table? An actor can appear in many films. A film has many actors. Should films and actors be joined somehow? If so, what type of join do you need? A director can direct many films. Are there any films with multiple directors? What type of join for directors to films? People have names. Films have names. Films have ratings (U, PG, 12, 15, 18 - or whatever it is in your country).
flemmard wrote: How can i add photos to this database ??
You haven't mentioned WHAT database you are using.
|
|
|
|
|
thnx guyz....btw, i accpet itz a homework.....but still i m trying to know atleast something from u all as i juz started off with SQL....btw, i m totally confused with the idea , which u gave me ...can u explain with tables (diagram) plzzzz...and for the foto, wat do u mean by blob ?
i m using MYSQL for my homework ....
thnx again guyz
|
|
|
|
|
flemmard wrote: thnx guyz btw itz i m u juz plzzzz foto wat
You know it takes more time to interpret what you are saying if you use this bizzare "txtspk". Please write in proper English, the majority of people here that can help you are professionals.
thnx = thanks
guyz = guys
btw = by the way,
itz = its
i m = I'm
u = you
juz = just
plzzzz = please
foto = photo
wat = what
flemmard wrote: i accpet itz a homework
Then you'll have to actually learn it. If I just give you the answer then you don't learn. You learn by doing.
flemmard wrote: an u explain with tables (diagram)
This forum does not offer the facility to draw diagrams. ASCII art is the best you can get.
flemmard wrote: for the foto, wat do u mean by blob
BLOB is a standard abbreviation for Binary Large OBject. Many database systems have a column type known as BLOB, image, binary, varbinary etc. for this. Usually it means a column that is able to accept upto 2Gb per row.
Now, why don't you go back and try and work out a data model for your movie catalog. The rules are:
1. Each column must contain a single piece of information. e.g. A person's name should be split out into first name, middle names, surname, or an address should be split out so that Street, Town, County, Country, Postcode are in separate columns. NOTE: In case you are tempted, dates have their own column type and are regarded as a single piece of information - do not split out the day, month and year.
2. Each table has a primary key that, by itself, can uniquely identify the row. Typically, this means creating a column that increments on each insert. It is calledn an IDENTITY column in SQL Server - MYSQL may have another name for it.
3. All information in a row is dependent on the primary key. In other words, if you have a table representing a person then First name, Surname and Date of Birth are valid columns, Address columns are not and should be put in a different table because you can have more than one person staying at the same address. If you keep address in the same table as the person you will end up having the same address repeated several times in the one table. So, if you find you have the same information repeated in the table several times then it is a candidate for removal to a new table (or perhaps another existing table)
If you want help with your data model, you have to prove that you at least tried it. So, have a go at creating a data model and then post it here and we can help you on any problems you may be having, or we can point out areas that don't quite fit that you don't see yet. That way you will learn as you go rather than just accept a pre-done data model without really understanding it.
I hope this helps.
|
|
|
|
|
Can you tell me the given relational schema and the SQL are correct ? Need i to add anything ??
Relational Schema<br />
<br />
This Relational Schema consists of 3 entities and 2 relations.<br />
We use natural attributes as the primary keys of each entities, and we assume the following attributes are unique within it's relational schema.<br />
Name of Actor;<br />
Name of Director;<br />
(Title, Year) of Movie.<br />
<br />
<br />
CREATE TABLE Movie (<br />
Title varchar(100), <br />
Year int(4), <br />
Genre varchar(100), <br />
Country varchar(100), <br />
Plot varchar(500), <br />
Poster varchar(1000)<br />
PRIMARY KEY (Title, Year));<br />
<br />
CREATE TABLE Actor (<br />
Name varchar (100) PRIMARY KEY, <br />
DOB date , <br />
Gender varchar(1),<br />
Nationality varchar(100));<br />
<br />
CREATE TABLE Director (<br />
Name varchar (100) PRIMARY KEY, <br />
DOB date, <br />
Gender varchar(1),<br />
Nationality varchar(100));<br />
<br />
CREATE TABLE Act (<br />
Movie_title varchar(100), <br />
Movie_year int(4), <br />
Actor_name varchar(100), <br />
PRIMARY KEY (Movie_title, Movie_year, Actor_name),<br />
FOREIGN KEY ( Movie_title, Movie_year) References Movie(Name, Year),<br />
FOREIGN KEY (Actor_name) References Actor (Name));<br />
<br />
CREATE TABLE Direct (<br />
Movie_title varchar(100), <br />
Movie_year int(4), <br />
Director_name varchar(100), <br />
PRIMARY KEY (Movie_title, Movie_year, Director_name),<br />
FOREIGN KEY (Movie_title, Movie_year) References Movie(Name, Year),<br />
FOREIGN KEY (Director_name) References Director (Name));<br />
<br />
|
|
|
|
|
Here is my critique.
flemmard wrote: We use natural attributes as the primary keys of each entities, and we assume the following attributes are unique within it's relational schema
As this is an academic exercise this is acceptable. However, in the real world "natural attributes" are often a poor choice unless they are numbers because keys should be as short as possible so that searches on the key waste as little time as possible pulling data off the disk. The use of variable length columns in a key is also prone to performance problems in a real world application.
The tables Act and Direct are poorly named. As this is an academic exercise this may not matter much. In the real world it is better to have the many-to-many intermediate tables named after their source tables (e.g. MovieActor) so that the schema is easier to maintain.
What happens if you have a film where the same person is a Director and an Actor? (e.g. Star Trek Insurrection. Jonathan Frakes is both the Director and the Actor playing Cmdr. Riker.) You may wish to normalise this better. (A tell-tale sign is that the structure of the Actor and Director table are the same) In a real world scenario it may be acceptable to denormalise the model somewhat for performance advantages, but maybe not for an academic exercise where correctness of model is more important than performance of model.
Country is a varchar(100) - There are roughly 200 countries in the world, the data in this column will be repeated a lot. (The same goes for Genre and Nationality) Consider refactoring this to a separate lookup table with a small-integer as the key. However, if you are having "natural attributes for keys" then there is not really any benefit - I mostly use counters, identity columns and Guids for keys.
|
|
|
|
|
Hi thanks for your reply. By the way, can you give me some hint how to retrive the photo from the disk for a particular movie ? I really don't know how to use the BLOB .\
Thanks.
|
|
|
|
|
flemmard wrote: By the way, can you give me some hint how to retrive the photo from the disk for a particular movie ? I really don't know how to use the BLOB
You need to store in some sort of binary column. I don't use MYSQL so I really can't say what it calls the column type. (binary, varbinary, image, blob are all common names for this type of thing). When you send it from your application you need to put it in a byte[] (C# := byte array), and when you retrieve it you'll get a byte array back, so you'll have to convert it in and out of a byte array before you can use it. The Bitmap class has the methods to do this for you. (I am, of course, assuming you are using the .NET Framework)
|
|
|
|
|
This sounds like homework, your a very naughty boy.
Create table Movies with movieid, desc, year, etc....
create table actor with actor id, name, etc...
create a MovieActor table which links the two tables Movies and Actor ( it will look like Id, MovieID, ActorID).
The same for directors....
Images is a blob or image files
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Frank Kerrigan wrote: your a very naughty boy.
Another flashback to Monty Python: He's not the messia, he's a very naughty boy!
|
|
|
|
|
Only messia would say he's not the messia.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
|
I can't believe I ran it, but looks like a little error in the above script.
The CREATE DATABASE process is allocating 2.00 MB on disk 'Online Mall_Data'.
The CREATE DATABASE process is allocating 1.00 MB on disk 'Online Mall_Log'.
Msg 207, Level 16, State 1, Procedure add_products, Line 25
Invalid column name 'Date_added'.
Msg 207, Level 16, State 1, Procedure update_products, Line 25
Invalid column name 'Date_added'.
I'm not going to debug it for you.
I hope this is a homework project, and not a commercial application you've posted online for the world to see! If you were my employee, I'd be a bit miffed if you'd just posted my database schema online.
You'll probably get better feedback if you stated what your aim was with the logic (giving specific examples), rather than just posting the database script.
ChrisB
|
|
|
|
|
yes it's a homework project
thank you for your time and help
Mohsen
|
|
|
|
|
Jeez - Well, what is it meant to do?
|
|
|
|
|
I think its a database for a MALL, though I'm not sure.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Frank Kerrigan wrote: I think its a database for a MALL
That reminds me of a joke:
A pregnant woman realises that she can't look after the identical twins she is expecting so she puts them up for adoption. Many years later she decides that she wants to meet them to see what they are like. She finds out that one has been adopted by a jewish family who named him Amal, the other by a Spanish family who named him Juan. She eventually manages to get in touch with the Spanish family and she meets Juan. However, she was never able to track down the current whereabouts of the Jewish family. She asks her husband how she is ever going to know what Amal looks like and he replied "If you've seen Juan you've seen Amal"!
Boom! Boom!
|
|
|
|
|
Im looking for a query who get result in packages
let me explain
select * from users where codgroup = 1
i get all the user belongs to group 1 (100 users)
i want a query who get the rows by packages ( 10 by 10 users).
I hope you can help me
PD: Only sql statements, not using paging gridview property
keep Learning and you never will be out of date...
|
|
|
|
|
Britney S. Morales wrote: i want a query who get the rows by packages
I don't understand what you mean by "packages"
Do you mean you only want the SQL to return 10 rows at a time?
|
|
|
|
|
Britney S. Morales wrote: i want a query who get the rows by packages ( 10 by 10 users).
PD: Only sql statements, not using paging gridview property
What's a package ? : (stupid answers on a postcard please )
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I am guessing that you want something like this:
select <code>TOP 10</code> * from users where codgroup = 1 <code>AND </code><code>usersortfield > 'last groups greatest value' ORDER BY </code><code></code><code>usersortfield</code> EXAMPLE:
If you were ordering by LastName, you would do this to get the first 10:
select <code>TOP 10</code> * from users where codgroup = 1 </code><code><code>ORDER BY </code></code><code><code><code></code></code>LastName</code><code> Then, when you want the next 10, you would take the lastname from the last record in the previous 10 and:
select <code>TOP 10</code> * from users where codgroup = 1 </code><code>AND </code><code><code></code><code><code></code></code>LastName <code></code></code><code>> </code><code><code>'</code><font class="messagetitle">Morales</font></code><code>' </code><code><code>ORDER BY </code></code><code><code><code></code></code>LastName
(Of course, you wouldn't use LastName alone, because it isn't unique enough...but you can see what I mean by this example.)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Eric thats the point, it isnt the answer, but your post given to me a light in the dark,
(its just a sentence) 
keep Learning and you never will be out of date...
|
|
|
|
|
I get
"System.NotSupportedException: The method specified is not supported by the current provider."
AdomdClient v8, VS 2003, SQL Server 2000
What's the cause of this and is there any way around it?
Kevin
|
|
|
|
|
I've been retained by a company to create a HIPAA compliant billing file from their current data. The useless government information guide (A useless government manual, how could that be?) they were provided with is about 2 phone books thick and would make an excellent campfire.
Does anyone have any experience working with HIPAA billing files? Any advice would be appreciated - excellent advice would be compensated! 
Thanks
John
|
|
|
|
|
just a quickie...
Is there a feature in ms sql server that allows you to setup a stored-proc to be run at predefined times.. Im thinking along
the lines of the "scheduled tasks" in windows where you can choose to run a process every so often.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
