DBS211_Lab01_RelationalModel_W24

docx

School

Seneca College *

*We aren’t endorsed by this school

Course

211

Subject

Business

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by DeanCrabMaster1893

DBS211 – Introduction to Database Systems Winter 2024 Lab 01 – Relational Model Objectives: The purpose of the first lab of DBS211 is to familiarize yourself with the User Interface, SQL Developer, and the database that we will be using throughout the course to communicate with the Oracle server. By the end of this lab you should be able to:  Successfully establish a connection with and login to the Oracle database server using SQL Developer  Explore and work with the database and data  Understand the relationships, constraints, data types, and tables’ specification. Preface: If you have not already done so, you will need to download the sample database creation script from blackboard and run it. These instructions are included in the Getting Started section with SQL Developer document. LAB 01 - SUBMISSION Answer the following questions in the provided space. Save your file as a PDF file and name it as following : DBS211_L01_LastName.sql. Tasks: By navigating through SQL Developer and looking at the Columns, Data, model, and Constraints tabs for the given tables. You will answer the following questions. NOTE: In Question (a), some questions are answered as examples. You need to complete the rest. Add more rows to the tables in the document if you need more space for an answer. Use a different color for your answers. For the given tables in your database, answer the following questions: Part A See the sample question: a) Answer the following Question for the DBS211_PAYMENTS table. 1) How many columns (attributes) are there in this table? ____ 4 __________ 2) How many rows are there in this table? ______ 50 ___________ 3) List the table’s columns and the requested information in the following format: Column Name Type Not Null CUSTOMERNUMBER NUMBER(38,0) YES CHECKNUMBER VARCHAR2(50 BYTE) YES PAYMENTDATE DATE YES AMOUNT NUMBER(10,2) YES

DBS211 – Introduction to Database Systems Winter 2024 4) Sort the data based on the third column in your table and write the data of the first row in the following format. To sort the data based on a column, right click on that column and select “sort”. You can select the column that the data will be sorted based on it. (Make sure CHATACTER type values are enclosed in single quotes.) Column name Column Value CUSTOMERNUMBER 363 CHECKNUMBER ‘IS232033’ PAYMENTDATE 16-JAN-03 AMOUNT 10223.83 5) List all constraints in this table. If a constraint is a foreign key, write the reference table. Constraint Name Constraint Type Constraint on Column Constraint Condition Reference Table DBS211_PAYMENTS_CUSTNUM_F K Foreign_Key CUSTOMER_ID DBS211_CUSTOMERS SYS_C001034315 Check "CUSTOMERNUMBER" IS NOT NULL SYS_C001034316 Check "CHECKNUMBER" IS NOT NULL SYS_C001034317 Check "PAYMENTDATE" IS NOT NULL SYS_C001034318 Check "AMOUNT" IS NOT NULL SYS_C001034319 Primary_Key 6) What tables are in relationship with this table? List them below. Table Name Column in Common DBS211_CUSTOMERS CUSTOMER ID 7) What is the model for this table relationships? NOTE: means MANY means ONE MANY ( ) is close to Contacts. You read “many Contacts”. ONE ( ) is close to customers. You read “one customer”.

DBS211 – Introduction to Database Systems Winter 2024 8) Translate the relationships in Question 7 (model) to English. A customer have many payments. A payment refers to one customer. b) Answer the following Question for the DBS211_CUSTOMERS table. 1) How many columns (attributes) are there in this table? 13 2) How many rows are there in this table? ____________122_____ 3) List the table’s columns and the requested information in the following format: Column Name Type Not Null CUSTOMERNUMBER NUMBER(38,0) Yes CUSTOMERNAME VARCHAR2(50 BYTE) Yes CONTACTLASTNAME VARCHAR2(50 BYTE) Yes CONTACTFIRSTNAME VARCHAR2(50 BYTE) Yes PHONE VARCHAR2(50 BYTE) Yes ADDRESSLINE1 VARCHAR2(50 BYTE) Yes ADDRESSLINE2 VARCHAR2(50 BYTE) No CITY VARCHAR2(50 BYTE) Yes STATE VARCHAR2(50 BYTE) No

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DBS211 – Introduction to Database Systems Winter 2024 POSTALCODE VARCHAR2(50 BYTE) No COUNTRY VARCHAR2(50 BYTE) Yes SALESREPEMPLOYEENUMBER NUMBER(38,0) No CREDITLIMIT NUMBER(10,2) Yes 4) Sort the data based on the third column in your table and write the data of the first row in the following format: (Make sure CHATACTER type values are enclosed in ‘single quotes’.) Column Name Column Value CUSTOMERNUMBER 249 CUSTOMERNAME ‘Amica Models "&" Co.’ CONTACTLASTNAME ‘Accorti’ CONTACTFIRSTNAME ‘Paolo’ PHONE 011-4988555 ADDRESSLINE1 ‘Via Monte Bianco 34’ ADDRESSLINE2 Null CITY ‘Torino’ STATE Null POSTALCODE ‘10100’ COUNTRY ‘Italy’ SALESREPEMPLOYEENUMBER 1401 CREDITLIMIT 113000 5) List all constraints in this table. If a constraint is a foreign key, write the reference table. Constraint Name Constraint Constraint on Constraint Reference

DBS211 – Introduction to Database Systems Winter 2024 Type Column Condition Table CUST_SALESREP_ F K Foreign_Ke y SALESREPEMPLOYEENUMB E EMPLOYEE S “CUSTOMERNUMBER ” IS NOT NULL 6) What tables are in relationship with this table? List them below. Table Name Column in Common 7) What is the model for this table relationships? NOTE: means MANY means ONE 8) Translate all the relationships in Question 7 (model) to English. c) Answer the following Question for the DBS211_EMPLOYEES table. 1) How many columns (attributes) are there in this table? ______________ 2) How many rows are there in this table? _________________ 3) List the table’s columns and the requested information in the following format:

DBS211 – Introduction to Database Systems Winter 2024 Column Name Type Not Null 4) Sort the data based on the third column in your table and write the data of the first row in the following format: (Make sure CHATACTER type values are enclosed in single quotes.) Column Name Column Value 5) List all constraints in this table. If a constraint is a foreign key, write the reference table. Constraint Name Constraint Type Constraint on Column Constraint Condition Reference Table 6) What tables are in relationship with this table? List them below. Table Name Column in Common 7) What is the model for this table relationships? NOTE: means MANY means ONE

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

DBS211 – Introduction to Database Systems Winter 2024 8) Translate all the relationships in Question 7 (model) to English. d) Answer the following Question for the DBS211_ORDERS table. 1) How many columns (attributes) are there in this table? ______________ 2) How many rows are there in this table? _________________ 3) List the table’s columns and the requested information in the following format: Column Name Type Not Null 4) Sort the data based on the third column in your table and write the data of the first row in the following format: (Make sure CHATACTER type values are enclosed in single quotes.) Column Name Column Value 5) List all constraints in this table. If a constraint is a foreign key, write the reference table. Constraint Name Constraint Type Constraint on Column Constraint Condition Reference Table

DBS211 – Introduction to Database Systems Winter 2024 6) What tables are in relationship with this table? List them below. Table Name Column in Common Refers to 7) What is the model for this table relationships? NOTE: means MANY means ONE 8) Translate all the relationships in Question 7 (model) to English. Part B Create a relationship diagram for all the tables in the database. Use the MODEL tab to see the tables (entities) and their relationships. Your diagram must include:  All 8 tables  The names of the entities (tables)  The attributes (columns) for each table  Lines representing the relationships between tables  Crows Foot Symbols on the lines representing the type of relationship (1-1, 1-many)  Required fields should be bolded  Primary Key fields should be underlined or indicated with a PK beside it.

DBS211 – Introduction to Database Systems Winter 2024  Child fields in the relationships should be indicated with an FK beside it. Use Lucidchart to draw you diagram. Save the diagram as an image and insert it here in the following box. Good Luck.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents

OB Consulting Assignment Report Layout and Instructions - BUS1021-24W-Sec8-Organizational Behaviour.

BADM 3113 Writing Assignment 2 fa2370 A (1).doc

email .docx

Funding and Producing a Music Video.docx

BSBOPS601 - Business Plan Template.v1.0.docx

Simternship Round 2.docx

DBS211_Lab03_DDL_W24.docx

PROJECT THREE.docx

BUS210 6-3 Assignment Organizational Structure.docx

BUS225_Module_Two_Assignment.docx

BA 102 Bizcafe Decision #3 (1).pdf

Week 2 Assignment (CH).docx

Recommended textbooks for you

BUSN 11 Introduction to Business Student Edition

Business

ISBN:9781337407137

Author:Kelly

Publisher:Cengage Learning

Essentials of Business Communication (MindTap Cou...

Business

ISBN:9781337386494

Author:Mary Ellen Guffey, Dana Loewy

Publisher:Cengage Learning

Accounting Information Systems (14th Edition)

Business

ISBN:9780134474021

Author:Marshall B. Romney, Paul J. Steinbart

Publisher:PEARSON

Introduction to Business

Business

ISBN:9781947172548

Author:OpenStax

Publisher:OpenStax College

International Business: Competing in the Global M...

Business

ISBN:9781259929441

Author:Charles W. L. Hill Dr, G. Tomas M. Hult

Publisher:McGraw-Hill Education

Bcom

Business

ISBN:9780357026595

Author:LEHMAN, Carol M.

Publisher:Cengage Learning,

SEE MORE TEXTBOOKS

Recommended textbooks for you

BUSN 11 Introduction to Business Student Edition
Business
ISBN:9781337407137
Author:Kelly
Publisher:Cengage Learning
Essentials of Business Communication (MindTap Cou...
Business
ISBN:9781337386494
Author:Mary Ellen Guffey, Dana Loewy
Publisher:Cengage Learning
Accounting Information Systems (14th Edition)
Business
ISBN:9780134474021
Author:Marshall B. Romney, Paul J. Steinbart
Publisher:PEARSON
Introduction to Business
Business
ISBN:9781947172548
Author:OpenStax
Publisher:OpenStax College
International Business: Competing in the Global M...
Business
ISBN:9781259929441
Author:Charles W. L. Hill Dr, G. Tomas M. Hult
Publisher:McGraw-Hill Education
Bcom
Business
ISBN:9780357026595
Author:LEHMAN, Carol M.
Publisher:Cengage Learning,