how to create this table in sql server?

by

may be this will help you:

CREATE TABLE COUNTRY(
    country VARCHAR(255) PRIMARY KEY,
    language VARCHAR(255),
    timezone TIMESTAMP,
    currency FLOAT
)

CREATE TABLE REGION(
    region VARCHAR(255) PRIMARY KEY,
    landtype VARCHAR(255),
    country VARCHAR(255) FOREIGN KEY REFERENCES COUNTRY(country),
    scenery VARCHAR(255),
    page INTEGER UNIQUE, 
)

CREATE TABLE RESORT(
    resort VARCHAR(255) PRIMARY KEY,
    region VARCHAR(255) FOREIGN KEY REFERENCES REGION(region),
    transfertime TIMESTAMP,
    beach VARCHAR(255),
    beachnum VARCHAR(255),
    page INTEGER FOREIGN KEY REFERENCES REGION(page)
)

CREATE TABLE STDBASIS(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(3),
    description VARCHAR(255) 
)

CREATE TABLE HOTELS(
    hotelid BIGINT AUTO_INCREMENT PRIMARY KEY,
    hotelname VARCHAR(255),
    sunbeam VARCHAR(255),
    ya VARCHAR(255),
    rating VARCHAR(255),
    stdbasis VARCHAR(255) FOREIGN KEY REFERENCES STDBASIS(id),
    page INTEGER FOREIGN KEY REFERENCES REGION(page), 
    resort VARCHAR(255) FOREIGN KEY REFERENCES REGION(resort),
    resortloc VARCHAR(255),
    roomtotal INTEGER,
    CONSTRAINT chk_stdbasis CHECK (stdbasis BETWEEN 0 AND 4)
)

CREATE TABLE FACILITIES(
    facid VARCHAR(255) PRIMARY KEY,
    description VARCHAR(255),
    category INTEGER
)

CREATE TABLE FACINRESORT(
    resort VARCHAR(255) FOREIGN KEY REFERENCES REGION(resort),
    facid VARCHAR(255) FOREIGN KEY REFERENCES FACILITIES(facid),
    CONSTRAINT pk_rID PRIMARY KEY (resort,facid)
)

CREATE TABLE FACINHOTEL(
    hotelid VARCHAR(255) FOREIGN KEY REFERENCES HOTELS(hotelid),
    facid VARCHAR(255) FOREIGN KEY REFERENCES FACILITIES(facid),
    numof INTEGER,
    CONSTRAINT pk_hID PRIMARY KEY (hotelid,facid)
)

Please change datatypes and primary/foreign keys as per your business problem

you need to insert data in STDBASIS table as below:

INSERT INTO STDBASIS
(code, description) 
VALUES 
("hb", "Half Board"),
("bb", "Bed&Breakfast"),
("fb", "Full Board"),
("ao", "Apartment Only")

you need to insert data in FACILITIES table as below: 

INSERT INTO FACILITIES
(facid , description) 
VALUES 
("a", "accommodation"),
("e", "entertainment"),
("s", "sport"),
("m", "meals"),
("c", "children"),
("b", "bedroom")

More Related Contents:

  1. What is the purpose of system table master..spt_values and what are the meanings of its values?
  2. How to create multiple one to one’s
  3. SQL – How to store and navigate hierarchies?
  4. Why (and how) to split column using master..spt_values?
  5. Should I design a table with a primary key of varchar or int?
  6. Composite Primary key vs additional “ID” column?
  7. Can I set ignore_dup_key on for a primary key?
  8. Should I use SQL_Variant data type?
  9. What does ON [PRIMARY] mean?
  10. Why & When should I use SPARSE COLUMN? (SQL SERVER 2008)
  11. Is there a severe performance hit for using Foreign Keys in SQL Server?
  12. Single or multiple databases
  13. can we have a foreign key which is not a primary key in any other table?
  14. Add a column with a default value to an existing table in SQL Server
  15. Table Naming Dilemma: Singular vs. Plural Names [closed]
  16. Simplest way to do a recursive self-join?
  17. How to set table name in dynamic SQL query?
  18. TSQL PIVOT MULTIPLE COLUMNS
  19. Is there ever a time where using a database 1:1 relationship makes sense?
  20. Can a sql server table have two identity columns?
  21. Saving changes after table edit in SQL Server Management Studio
  22. Logging ALL Queries on a SQL Server 2008 Express Database?
  23. SQL query to find Nth highest salary from a salary table
  24. Get the records of last month in SQL server
  25. Access to Result sets from within Stored procedures Transact-SQL SQL Server
  26. alternatives to REPLACE on a text or ntext datatype
  27. How to create a Date in SQL Server given the Day, Month and Year as Integers
  28. Count records for every month in a year
  29. What is the best way to implement soft deletion?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment