Blog Tutorial 5 - ERD

Exercise

1) Draw a complete  ER Diagram for the following:

A customer reserve a date for the maintenance / repairs to a vehicle . The reservation is given Reservation_No , Customer_ID and Vehicle_No are recorded with the reservation

The Time_Off_Reservation is also recorded. Information stored about cuatomer indudes Customer_Id , Customer_Name , Address and Telephone_No. Information kept about the vehicle includes. Vehicle_No make, Reg_No and Date_of_Manufacture.  After examination , a number of jobs are recorded for the vehicle. Each job has Job_No within the booking and reasons for carrying out the job are recorded as why_needed. The parts used for each job and the time. Spent on each job a are also recorded . The information about parts include Part_No and price.

2)a) What is the definition of relational keys and why it is needed?

Relational key consist of one or more attributes that can be identified in a row. Relational key is an attribute that relates one entity in a relationship. Relational key needed because it enables us to link the tables together in a database for easier data access storage.

b) List and explain 4 types of relational keys.

i)Candidate key-An attribute or group of attribute that identify a unique row in a relation. One of the candidate key is chosen to be Primary Key

ii)Primary key-Any of the candidate that are selected to be the key or an attribute that uniquely identifies each row in a relational table

iii)Foreign Key-An attribute in one table whose value must match the primary key in another table or whose value must not be null.

iv)composite Key-A primary key that consist of more than one attribute, especially in (M:N) relationship

c) Based on the table given above, answer all the following questions:-



d) What is the name for the above table?

BORROWER

e)What is the Primary key for the above table

BORROWER_ID

f) What is data type for the borrower’s name, category, age and date of birth columns?

BORROWER_NAME        -              TEXT

CATEGORY                      -              TEXT

AGE                                  -              NUMBER

DATE_OF_BIRTH              -              NUMBER

g) How many attributes and records contains in the table?

ATTRIBUTE          -              7

RECORDS             -              2

3)Based on the diagram below, draw a complete entity relationship diagram (ERD) which include entity attributes, relationship, cordinality constraints and key attribute

REPORT ON THE RENTAL

OWNER NO : 40012

OWNER NAME : Fatimah Abdul Rahman

4) Everyday, there are 5 doctors is on duty in Orthopedic Wards. They are working on shift A. A doctor can give a treatment  to their patients and each patients can received a treatment from many doctor based on type of diseases. A patient can take a medicine but much medicine could be taken by many patients.

i)Doctor information is Doctor_ID and DoctorName

ii)Patient information is Patient _ID, PatientName and Address

iii)Medicine information is MedicineCode and MedicineName

iv)For each treatment. It had TypeOfTreatment and CostOfTreatment

v)For each accepted medicine, it has a Quality and Date.

Create one complete ERD.



5)

Create a complete ERD which include entities, attributes, relationship, reletional keys and cordinality constraints

6)

i)How many tables exist in the database?

4

ii) how many records in the T_salesInfo tables

3

iii) How many fields consist in the T_Supplier tables?

12

iv) Is SU00A1 a valid data for Supplier in the T_Supplier tables?

Yes



7)a) What is the meaning of database and  database management system (DBMS)?

 Database is a collection of related data, designed to meet the information needed in an organization and stores it in one specific location.

Database management system is a collection of program that enable users to create and maintain a database and provide control access to the database.

   b) There are 4 types of attributes list down and give definition , example and symbol.

8)

Create Complete ERD

9) Nation warehouse is supplier that supplies many kinds of product to retailers. All stocks are supply by many manufactures depends on types of products before it is distributed to all retailers.

Currently the company is using manual system to store information on inventory and stock delivery where by the process of ordering the products only will be done when stock is reach required level and it is calculated based on quantity left at teh rack. This process is consumes a lot of time and the company decided to develop a database system to assist them in storing and updating related information. The following are requirement for the system:-

All information about manufactures need to be stored such as ManufacturerID, companyName, Address, Telephone Number and fax number. Information about retailers also need to be kept such as retailerID, companyName, address, telephone number and fax number. Product information also will be recorded such as productCode, typeOfProduct, productName, SeilingPrice, RetailPrice and Quantity in stock.

All information for each order that made to manufacturer need to be recorded such as order number, date of order, quantity ordered for each product code and overall total

All information for delivering products ordered made by manufacturer need to be kept such as delivery number, date of delivery, overall total and manufacturerID. Each delivery made can contain many kind of products and must have product code, quantity delivered for each product, delivery number and subtotal.

Information about purchasing made by retais also need to be kept such as productCode, retailerID, Quantity and subtotal. Invoice will be giving to the retailer that contains invoice number, invoice date and overall total

Weekly Reflection 5

Today Mis Ayu gave us exercise on ERD. Gave us exercise to improve our understanding about ERD. If we understand about the passage well, we will able to do the exercise.  First you need to know the Entity after that you write down the attribute for te the Entity. After that, look for the Relationship for the Attribute. Than sketch the ERD. State the Cardinality Contraints. Specify the Primary Key.

Blog Tutorial 4 - report

Report

Create Report Using Report Wizard

To Create Report

v  Click on Report > click on Create Report By Using Wizard

v  Select a table under table / queries > select a field > click > button than click next > you will see a grouping data

v  click on the field to group report data > click next

 

v  click to sort the data > click on the field to be sorted > click next

v  click on the layout > click on the page orientation > click next

v  click on the that you want to use > than click next

v  type your title > and click finish button

v  click on the Design View Button

To Adding A Picture

v  Click on the toolbar > click add picure > click add location that you want to place your picture > insert picture form will appear > insert the picture that want > save

To Renaming A report

v  Click on the report > right click the name of the report > click on the rename > type the name that you want to type > below you will see the different name.

Weekly Reflection 4

My 4th week today. Today Mis Ayuni want us to hands on, on our own for the new topic Report. Since i have already know the method so i able the to the report by my own. Mis Ayuni check 1 by 1 of us whether we are doing our work correctly. She also explain to us about our final project. Since me and Sara are the partners for this project, it will be convinience for us to discuss and do the project together. We also discuss certain points about the project. After the explain about the project, she went to new lesson which is Entity Relationship Modeling. She explain to us the meaning of Entity, Relationship, Attribute, Multivalue Attribute, Composite Attribute and Derived Attribute. She also explain to us about the types of related key and types of integrity rules. She gave us some exercise on this current topic which will be a bit similar question for our mid Term exam . 

Blog Tutorial 3 - Forms

Forms

Create Forms By using Wizard

To Create Forms From One Table

v   Click on Forms > Click on Create Forms By Using Wizard > you will see a Form Wizard.

v  select the table containing the field > choose the field that you want to include

v  you will see the form

To Edit Data In form

v  click on the cell where you want to edit the data > put the cursor there > enter BACKSPACE key > than type the new data

To Add A Record

v  Click on the add button > your cursor will move to the empty cell > type the data > click save

To Delete A Record

v  Click on the field of the record to be deleted > click on delete button > a warning dialog box appear >

    click yes > the record disappear

To Change View

v  Click on Design View > form displayed in design view

To Change The Size Form

v  place your mouse over the right edge of the form > drag the edge until the wanted size > below you will see the different

Weekly Reflection 3

Today 3rd week of DADD. Today Mis Ayuni Teach us about Forms. I was so excited that i'm going to learn new topic today. As usual i open my computer at lab but the computer cannot open. That the only computer i save my work for past 2 weeks. Its my bad day that day. I was realy sad. my fren Sara, she brought her laptop today. Its good to have own laptop. I waiting for cosmo to give the laptop. Since she brought her laptop, we share and do todays lesson.

Blog Tutorial 2 - Query

 Queries

Create Query In using Design View

To Create Query

v  Click on Queries > Click on Create Query In design View > you will see a Table dialog Box.

v  Choose the information you want > click the add button > selected query will display > after you finish select > click close button

To Delete The Table

v  Select the Query that You want to delete > click the table > then press the delete key it will remove from the Query but not from the database.

To Insert The Table

v  place the cursor at the epmty place > right click > choose show table > a Show Table Dialog Box will appear > choose the Query that you want, for example Booking > than click add button > click close button

To Insert The Field

v  Click a field that you want to include in  your query > Double click the field that you choose > Below are the field that will be insert in the query

v  Press ! button to run the Query > you can see the result below:

To Save

v  Click on the save button  > you will see a save as dialog box > type the Query Name > click Ok > you will see the Query name.

 

Create Query By using Wizard



To Create Query

v  Click on Queries > Click on Create Query By using Wizard > you will see a Simple Query Wizard box.

v  Choose the table > click the field that you want to include > click > button to add one by one or click

     >> to add all the field > you will see the field that you add, at the box beside.

v   Select another Table under Customer

v  Click Next button to go to the next page of Query Wizard > you will see a Simple Query Wizard box > type the Title for your Query > Choose Open the query to view information > click finish

To Change a Query

v  Click on the Design View symbol to change to Design View .

 To Rearrange Fields

v  Click the field you want to move > drag the field to a new location > click save button to save the changes.

To Delete the Fields

v  Click the field that you want to delete > and press DELETE key > click on the save button to save the changes.

 Ø Click on the datasheet symbol to see the result