0

I started database a few weeks back so im basically a potato right now! Anyway i encountered this problem in the exercises but i wasnt sure on how to do some of the steps.

I have to create a table in SQL with these:

USER(SSN, Name, Surname, YearOfBirth)

CONTENT (CodC, Category, Duration, Title, Description*)

RATING (SSN, CodC, Date, Evaluation)

The primary keys are shown but i dont know how to underline in here. I also had these Notes to follow:

• CodC is an incremental positive integer identifer (1, 2, etc.).

• Duration is in minutes.

• Evaluation contains the level of satisfaction expressed on a scale from 1 to 10.

I know I have not completed the creation of the tables since i have to define the Primary Keys and Foreign Keys but that extremely easy to do but right now but what i need help with is HOW do i write the Duration to be explicitly in minutes and for CodC to have incremental positive integer identifier. Can you help me?

SO what i did was:

CREATE TABLE User (
  SSN CHAR (20) NOT NULL,
  Name CHAR (50) NOT NULL,
  Surname CHAR (50) NOT NULl,
  YearOfBirth Year NOT NULL,
)

CREATE TABLE Content (
 CodC int(10) IDENTITY(1,1),  // the identity thing i copy-pasted it from the net
 Category varchar(30) NOT NULL,
 Duration smallint(20) NOT NULL,
 Title varchar(20) NOT NULL,
 Description varchar(20) NOT NULL,
)


 CREATE TABLE Rating(
  SSN CHAR (20) NOT NULL,
  CodC int(10) NOT NULL,
  Date varchar(20) NOT NULL,
  Evaluation smallint(20) NOT NULL,
  CONSTRAINT lev_constr CHECK ( Evaluation >=1 AND Evaluation <=10)
);
Ani Lici
  • 1
  • 3
  • Does this answer your question? [How to create id with AUTO\_INCREMENT on Oracle?](https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) – sticky bit May 31 '21 at 23:11
  • We have not covered the triggers yet in class. What about the minutes thing EDIT: Actually it does! – Ani Lici May 31 '21 at 23:15
  • "What about the minutes thing" -- Yeah, what about it? Just use any numeric type specific enough for you. Likely some integer will do as fractions of minutes are rarely used. What's the exact problem you have with that? – sticky bit May 31 '21 at 23:18
  • "We have not covered the triggers yet in class." -- I think in 11g triggers are needed, there's no other way. – sticky bit May 31 '21 at 23:19
  • @stickybit For the first one im just not sure how to write the condition in the create table. Im sorry if it sounds stupid but im a beginer – Ani Lici May 31 '21 at 23:24
  • It doesn't sound stupid, but it doesn't explain what problem you have. It seems simple but maybe it isn't in your case. But you need to explain what's special in your case. If we don't understand that, we cannot give a good answer. – sticky bit May 31 '21 at 23:27
  • Ok so the problem goes like this! We have to create 3 tables with the given logical schema. There are some notes that are like "conditions" and the for the Duration in the 2nd table it is specifically required to be in minutes. Is there a way for me to make sure that when later I/USER fill the table the Duration is to be specifically in minutes? Does it make sense? – Ani Lici May 31 '21 at 23:32
  • Yeah but what are the constraints? Can a duration only be less than *n* but more that *m* minutes maybe? Without knowing such detail there's not much we can help with I'm afraid. – sticky bit Jun 01 '21 at 00:01
  • As a side observation, in oracle you should be using VARCHAR2, not VARCHAR. – EdStevens Jun 01 '21 at 00:26
  • In table RATING, you have a column named DATE. This is inavlid, as DATE is a reserved word. And if that column is supposed to be a date, then its datatype should be DATE, not any character type like VARCHAR. – EdStevens Jun 01 '21 at 00:32
  • @stickybit Sorry for the late reply! No there is no other constraint Just that it should be in minutes, only that! – Ani Lici Jun 01 '21 at 00:53
  • @EdStevens Date is the name of the column in trying to build. How do you propose i should write it? – Ani Lici Jun 01 '21 at 00:54
  • If you define the column as `duration_in_minutes number(38,0)` you should consider the input is in minutes. If someone decide to insert 10 hours like `insert into mytab (duration_in_minutes) values (10)` you have **no any possible way** to stop him from this, because you, hopefully, cannot read someone's mind. But it is not a constraint violation, because you assume that input is prepared. – astentx Jun 01 '21 at 02:10
  • Varchar column *may look like* solution for the problem if you add constraints on format to force the user to prepare input values to be "time-like". But it makes not much sense, since the user can also "fool" such constraint with: "Ha-ha, I'll put hours in place of minutes!". It is the same. This is what was said in the first few comments. – astentx Jun 01 '21 at 02:15
  • _"Date is the name of the column in trying to build. How do you propose i should write it"_ -- My personal/professional naming standard is that all columns should be named in the format 'adjective_noun'. So your date column could be RATING_DATE. – EdStevens Jun 01 '21 at 02:24
  • 1
    Also, in your USER ... again 'USER' is a reserved word. You need a different name. And your data types should be VARCHAR2, not CHAR. CHAR is a fixed length type that will always be padded with spaces for any values of less than the specified length. And there is no data type YEAR. And while I understand this is just a homework, in real life there would be serious legal and security issues with using SSN in this manner. Better to just have a locally created id. Same with use of SSN in RATING table. – EdStevens Jun 01 '21 at 02:31

0 Answers0