im-20900 database management
DESCRIPTION
IM-20900 Database Management. The First Assignment. Teacher:Prof. WeiPang Yang Student: Wayne Chiu Number:410235014. Contents. Platform Introduction XAMPPs Configuring Phpmyadmin Using Create Databases Create Tables Correcting Data and Structure Error - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/1.jpg)
IM-20900Database ManagementThe First Assignment
Teacher: Prof. WeiPang YangStudent: Wayne ChiuNumber: 410235014
![Page 2: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/2.jpg)
Contents• Platform Introduction• XAMPPs Configuring• Phpmyadmin Using• Create Databases• Create Tables• Correcting Data and Structure Error• Simulate Situation for Database Practice
![Page 3: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/3.jpg)
Our Platform• A Standard X86 Server• 100Mbps Internet Connection• Linux Mint 16.0
• SELinux Disabled• Firewall Disabled
![Page 4: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/4.jpg)
Before Install XAMPP• ROOT privilege required• Incapable with Unix/BSD• Incapable with some Linux Distribution
• Red-Hat related or similar Linux Distribution• Service are related to below must be halted
• Web Servings• SQLs• FTPs
![Page 5: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/5.jpg)
Download XAMPP• Use Command: ‘wget’ to Download:
• Does not have wget:• Debian Based: apt-get install wget
• Other refer the man page to install wget• Download in your home directory by default
![Page 6: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/6.jpg)
Install XAMPP• Endow Executable properties:
• Original: (-rw-r--r--)
• Endow the properties: (-rwxrwxrwx):
![Page 7: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/7.jpg)
Install XAMPP• Execute the file as ROOT:
![Page 8: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/8.jpg)
Install XAMPP• Install Finished:
![Page 9: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/9.jpg)
Configure XAMPP• Does not bring up at startup by default:
• Suitable for Beginners• Keep Beginners Server Secure• If needed , refer the rc.conf
• Default directory of XAMPP:• /opt/lampp
• Start or stop XAMPP (ROOT Previlege):• /opt/lampp/xampp start• /opt/lampp/xampp stop
![Page 10: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/10.jpg)
Configure XAMPP• Example of XAMPP Start
![Page 11: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/11.jpg)
Login into phpmyadmin• Start X Windows
• Run Firefox• Refer to your X Windows Instruction• Terminal type “firefox” to execute.• Go to http://localhost/
• No firefox:• Debian Based: apt-get install firefox• Other refer the man page to install firefox
![Page 12: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/12.jpg)
Login into phpmyadmin• If Successful, you will see the webpage:
![Page 13: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/13.jpg)
Login into phpmyadmin• On the left, at the part of ‘Tools’click phpmyadmin
![Page 14: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/14.jpg)
Login into phpmyadmin• If Successful, you will see the webpage:
![Page 15: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/15.jpg)
Project: A computer store database• Including all information of a computer• Similar the cases as the same as the customer require• Correct errors, ether the datatype or data in Database.• Delete the record when computer was sold out.
![Page 16: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/16.jpg)
Create Databases
![Page 17: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/17.jpg)
Create Database• We can see by the sidebar as it was created.
![Page 18: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/18.jpg)
Create Database• But some small problems occurred:
![Page 19: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/19.jpg)
Create Database• What happened with Latin1?:
• Does not support Non-Latin Words:• That also means can’t store Chinese character• Some of the Computer Brands or Model are Chinese:• 清華同方 超瑞 X200
• Too much Non-Latins accidentally stored will crash MySQL
![Page 20: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/20.jpg)
Create Database• Change encoding options as UTF-8:
![Page 21: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/21.jpg)
Create Database• Why UTF-8?
• It is widely used on the world.• Most of the computer support this encoding method• Non-Latin words such as Chinese can be stored.
![Page 22: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/22.jpg)
Create Database• Corrected Database:
![Page 23: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/23.jpg)
Create Tables• Create a Table: Laptop, containing all info. Of Laptops
![Page 24: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/24.jpg)
Create Tables• Successfully Created:
![Page 25: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/25.jpg)
Create Tables• Structures:
![Page 26: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/26.jpg)
Inserting Datas• Inserting 3 Laptops Data:
![Page 27: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/27.jpg)
Inserting Datas• We expect the result as:
• But:
• Why?
![Page 28: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/28.jpg)
Correcting Structure Error• Because the Database we create:
They are integers, not float
![Page 29: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/29.jpg)
Correcting Structure Error• No Need to Delete, Correct them• Use: ALTER TABLE * MODIFY * [DATATYPE]
![Page 30: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/30.jpg)
Correcting Structure Error• Changed to float:
![Page 31: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/31.jpg)
Correcting Datas:• But Still:
• WHY?
![Page 32: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/32.jpg)
Correcting Datas• it is only ‘integer’allowed• Some was lost once the data was stored during that time.• We need to update Datas by ourselves again.
![Page 33: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/33.jpg)
Correcting Datas• Using Update to Correct:
![Page 34: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/34.jpg)
Correcting Datas• After several updates:
• Correct Result
![Page 35: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/35.jpg)
Situation – Customers Request• A Customer Request for computers under 20000NT$
• Resort Gives out:
![Page 36: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/36.jpg)
Situation – Customers Request• A Customer Request for any Computers bigger than 13 Inches• Also, He does not need too detail of computer specification
• Result:
![Page 37: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/37.jpg)
Situation – Sold• When a item was sold, it must be removed from database:• Aspire V3-551G was sold:
• Result:
![Page 38: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/38.jpg)
Thoughts:• Linux is a ultra-flexible platform, but somehow complex• I recommend XAMPP to beginners because to setup such
environment on Linux is a lot of hassle and sometimes need patients to troubleshooting.
![Page 39: IM-20900 Database Management](https://reader035.vdocuments.us/reader035/viewer/2022070501/56816935550346895de090fa/html5/thumbnails/39.jpg)
Thank you for appreciate your time