Something | About db-design

本文通过实例详细解析了数据库设计中的常见错误及如何利用数据库设计范式(1NF、2NF、3NF)来优化数据库结构,避免数据冗余、更新异常等问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >




我们在设计数据库的时候,可能会犯以下错误:
   比如,有这么一
个表:
学号姓名课程名称成绩学分
1张三语文803
2李四语文903
3王五语文563
1张三数学752
1张三物理825

1.数据冗余:
如果要修改课程的学分,则需要对所有相应的课程对应的学分都进行修改,多麻烦
2.更新异常
如果把张三的语文学分修改为5分,那么李四和王五就不服气了,凭什么一样的课程他张三就能得5个学分...
3.插入异常
假设要开一门新的课程,暂时没有人选修,则无法记录到表中,因为没有学号,(学号,课程名称可作为主键).
4.删除异常
假设一批学生选修的课程完成了,这些选修课的记录都应该从数据库中删除,但与此同时,课程名称和学分信息也被删除了.

数据库设计范式:
数据库设计常用范式
(1NF 2NF 3NF)
(1)
第一范式
数据库表中的字段都是单一属性的,不可再分。
(但也不是绝对的,反例如微软的Profile数据表的情况)

(2)
第二范式
数据库表中的一些非主键字段部分依赖于部分主键字段。(如上例中,主键应该为学号与课程名称,姓名依赖于学号,学分依赖于课程名称),此种情况只是针对主键是多个字段的情况。(反例:以上例子中,如果每学期的课程名称的学分不确定,可能会有变化,则这里另当别论)
 (3)
第三范式
数据库表中的一些非主键字段部分依赖于其它非主键字段。

 依据范式,我们可以修改上面的表为三个表:
1.------------------------------------------->满足2NF
学号姓名
1张三
2李四
3王五
2.------------------------------------------->满足2NF
课程学分
语文3
数学2
物理5
3.学生和课程之间的关系是多对多,我们实际设计数据库的时候,通常在两个表之间再建一个表,以两个表的主键来做第三个表的联合主键,显然这里不太适用.
学号课程成绩
1语文80
2数学null
3物理null
没关系,我们还有另一种方法---------------------------------------满足3NF
自动增长标识列学号课程成绩
11语文80
21数学75
31物理82
42语文90
53语文56
这里,学号和课程就不是联合主键的身份了

总结:
关于数据库设计的基本原则是:
1.正确反映数据与数据(信息与信息)之间的层次逻辑关系.
2.对进入到数据库中的数据有一个有效性检查.
3.对数据库中的数据进行非逻辑操作进行相应的错误处理.
4.满足系统对性能上的要求.
我们在设计数据库之前一般要根据需求来进行数据库分析,然后再去设计一个完善的数据库,分析的时候画出实体关系模型(ER-建模),我一般用ERStudio这个工具来进行ER建模.
<div align="center"> <h2>AFNet: Adaptive Fusion of Single-View and Multi-View Depth for Autonomous Driving</h2> <h2>**CVPR 2024**<h2> [Paper](https://arxiv.org/pdf/2403.07535.pdf) </div> This work presents AFNet, a new multi-view and singleview depth fusion network AFNet for alleviating the defects of the existing multi-view methods, which will fail under noisy poses in real-world autonomous driving scenarios. ![teaser](assets/pointcloud2.png) ## ✏️ Changelog ### Mar. 20 2024 * Initial release. Due to the confidentiality agreement, the accuracy of the current reproduced model on KITTI is very slightly different from that in the paper. We release an initial version first, and the final version will be released soon. * In addition, the models trained under noise pose will soon be released. ## ⚙️ Installation The code is tested with CUDA11.7. Please use the following commands to install dependencies: ``` conda create --name AFNet python=3.7 conda activate AFNet pip install -r requirements.txt ``` ## 🎬 Demo ![teaser](assets/visual_compare.png) ## ⏳ Training & Testing We use 4 Nvidia 3090 GPU for training. You may need to modify 'CUDA_VISIBLE_DEVICES' and batch size to accommodate your GPU resources. #### Training First download and extract DDAD and KITTI data and split. You should download and process DDAD dataset follow [DDAD🔗](https://github.com/TRI-ML/DDAD). #### Download [__split__ 🔗](https://1drv.ms/u/s!AtFfCZ2Ckf3DghYrBvQ-DWCQR1Nd?e=Q6qz8d) (You need to move this json file in split to the data_split path) [ models 🔗](https://1drv.ms/u/s!AtFfCZ2Ckf3DghVXXZY611mqxa8B?e=nZ7taR) (models for testing) Then run the following command to train our model. ``` bash scripts/train.sh ``` #### Testing First download and extract data, split and pretrained models. ### DDAD: run: ``` python eval_ddad.py --cfg "./configs/DDAD.conf" ``` You should get something like these: | abs_rel | sq_rel | log10 | rmse | rmse_log | a1 | a2 | a3 | abs_diff | |---------|--------|-------|-------|----------|-------|-------|-------|----------| | 0.088 | 0.979 | 0.035 | 4.60 | 0.154 | 0.917 | 0.972 | 0.987 | 2.042 | ### KITTI: run: ``` python eval_kitti.py --cfg "./configs/kitti.conf" ``` You should get something like these: | abs_rel | sq_rel | log10 | rmse | rmse_log | a1 | a2 | a3 | abs_diff | |---------|--------|-------|-------|----------|-------|-------|-------|----------| | 0.044 | 0.132 | 0.019 | 1.712 | 0.069 | 0.980 | 0.997 | 0.999 | 0.804 | #### Acknowledgement Thanks to Zhenpei Yang for opening source of his excellent works [MVS2D](https://github.com/zhenpeiyang/MVS2D?tab=readme-ov-file#nov-27-2021) ## Citation If you find this project useful, please consider citing: ```bibtex @misc{cheng2024adaptive, title={Adaptive Fusion of Single-View and Multi-View Depth for Autonomous Driving}, author={JunDa Cheng and Wei Yin and Kaixuan Wang and Xiaozhi Chen and Shijie Wang and Xin Yang}, year={2024}, eprint={2403.07535}, archivePrefix={arXiv}, primaryClass={cs.CV} } ``` PS E:\AFNet-main> pip install -r requirements.txt Defaulting to user installation because normal site-packages is not writeable Collecting imageio==2.9.0 (from -r requirements.txt (line 1)) Downloading imageio-2.9.0-py3-none-any.whl.metadata (2.6 kB) Collecting ipdb==0.12.3 (from -r requirements.txt (line 2)) Downloading ipdb-0.12.3.tar.gz (10 kB) Preparing metadata (setup.py) ... done Collecting joblib==1.0.1 (from -r requirements.txt (line 3)) Downloading joblib-1.0.1-py3-none-any.whl.metadata (6.8 kB) Collecting lz4==3.1.3 (from -r requirements.txt (line 4)) Downloading lz4-3.1.3.tar.gz (159 kB) Preparing metadata (setup.py) ... done Collecting matplotlib==3.1.3 (from -r requirements.txt (line 5)) Downloading matplotlib-3.1.3.tar.gz (40.9 MB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 40.9/40.9 MB 6.2 MB/s eta 0:00:00 Preparing metadata (setup.py) ... error error: subprocess-exited-with-error × python setup.py egg_info did not run successfully. │ exit code: 1 ╰─> [28 lines of output] C:\Users\HP\AppData\Local\Temp\pip-install-xl7u4dpy\matplotlib_e71ff03b933d48d99aa6e0525941cb19\setup.py:32: SetuptoolsDeprecationWarning: The test command is disabled and references to it are deprecated. !! ******************************************************************************** Please remove any references to `setuptools.command.test` in all supported versions of the affected package. This deprecation is overdue, please update your project and remove deprecated calls to avoid build errors in the future. ******************************************************************************** !! from setuptools.command.test import test as TestCommand Traceback (most recent call last): File "<string>", line 2, in <module> File "<pip-setuptools-caller>", line 34, in <module> File "C:\Users\HP\AppData\Local\Temp\pip-install-xl7u4dpy\matplotlib_e71ff03b933d48d99aa6e0525941cb19\setup.py", line 52, in <module> __version__ = versioneer.get_version() ^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HP\AppData\Local\Temp\pip-install-xl7u4dpy\matplotlib_e71ff03b933d48d99aa6e0525941cb19\versioneer.py", line 1410, in get_version return get_versions()["version"] ^^^^^^^^^^^^^^ File "C:\Users\HP\AppData\Local\Temp\pip-install-xl7u4dpy\matplotlib_e71ff03b933d48d99aa6e0525941cb19\versioneer.py", line 1344, in get_versions cfg = get_config_from_root(root) ^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\HP\AppData\Local\Temp\pip-install-xl7u4dpy\matplotlib_e71ff03b933d48d99aa6e0525941cb19\versioneer.py", line 401, in get_config_from_root parser = configparser.SafeConfigParser() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ AttributeError: module 'configparser' has no attribute 'SafeConfigParser'. Did you mean: 'RawConfigParser'? [end of output] note: This error originates from a subprocess, and is likely not a problem with pip. error: metadata-generation-failed × Encountered error while generating package metadata. ╰─> See above for output. note: This is an issue with the package mentioned above, not pip. hint: See above for details.
最新发布
06-04
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值